Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Email Attachements (2003)

    Hi,

    I am using the sendmail modules below to convert report to pdf and then attached to a new outlook email with To, CC, topic and body.

    Function SendMail( _
    Recipient As String, _
    CC As String, _
    BCC As String, _
    Subject As String, _
    Message As String, _
    Attachment As String, _
    Optional ShowMail As Boolean) As Boolean

    Dim objOL As Outlook.Application
    Dim objMI As Outlook.MailItem
    Dim blnNotActive As Boolean
    Dim Recip As Recipient
    Dim Counter As Integer
    Dim arr

    SysCmd acSysCmdSetStatus, "A moment please. Your e-mail message is being sent."
    DoCmd.Hourglass True

    ' Check whether Outlook is active
    On Error Resume Next
    Set objOL = GetObject(, "Outlook.Application")
    blnNotActive = (Err <> 0)

    If blnNotActive Then
    ' If not, we start Outlook
    Err.Clear
    Set objOL = CreateObject("Outlook.Application")
    End If

    On Error GoTo Err_Mail

    ' Create e-mail message
    Set objMI = objOL.CreateItem(olMailItem)
    With objMI
    arr = Split(Recipient, ";")
    For Counter = 0 To UBound(arr)
    Set Recip = objMI.Recipients.Add(arr(Counter))
    Next Counter
    arr = Split(CC, ";")
    For Counter = 0 To UBound(arr)
    Set Recip = objMI.Recipients.Add(arr(Counter))
    Recip.Type = olCC
    Next Counter
    arr = Split(BCC, ";")
    For Counter = 0 To UBound(arr)
    Set Recip = objMI.Recipients.Add(arr(Counter))
    Recip.Type = olBCC
    Next Counter
    .Subject = Subject
    .Body = Message
    .Attachments.Add Attachment, olByValue
    If ShowMail = True Then
    .Display
    Else
    .Send
    End If
    End With
    SendMail = True

    Exit_Mail:
    ' Release object memory
    On Error Resume Next
    If Not (objMI Is Nothing) And Not ShowMail Then
    objMI.Close olDiscard
    End If
    Set objMI = Nothing
    If blnNotActive And Not (objOL Is Nothing) And Not ShowMail Then
    objOL.Quit
    End If
    Set objOL = Nothing
    SysCmd acSysCmdClearStatus
    DoCmd.Hourglass False
    Exit Function

    Err_Mail:
    SendMail = False
    Resume Exit_Mail
    End Function

    The following codes is located at SendEmail button On click event:

    Dim strPDFFile As String

    strPDFFile = "c:Test"

    If ConvertReportToPDF(RptName:="rpt_Data", OutputPDFname:=strPDFFile, StartPDFViewer:=False) = True Then
    SendMail "to@to.com", "cc@cc.com", "", "Test", "Test", strPDFFile, ShowMail:=True

    End If

    Everything is works very well. Now I want to add other two rpt files that located at our share drive to the same new email attachement. What codes should I motify?

    Share Drive location: F:Test rpt file: ReportA.rpt; ReportB.rpt

    Thanks in advance.

    Regards

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Email Attachements (2003)

    Try the modified version of SendMail in the attached text file. Call it like this:

    SendMail "to@to.com", "cc@cc.com", "", "Test", "Test", Array(strPDFFile, "F:TestReportA.rpt", "F:TestReportB.rpt"), ShowMail:=True

    Note that the Attachment argument is an array of string values - one for each attachment.
    Attached Files Attached Files

  3. #3
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Email Attachements (2003)

    Thank you so much, Hans.

  4. #4
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Email Attachements (2003)

    I took the liberty of using the sample code in an Access database to create an email.

    Function SendMail( _
    Recipient As String, _
    CC As String, _
    BCC As String, _
    Subject As String, _
    Message As String, _
    Attachment As String, _
    Optional ShowMail As Boolean) As Boolean

    Dim objOL As Outlook.Application
    Dim objMI As Outlook.MailItem
    Dim blnNotActive As Boolean
    Dim Recip As Recipient
    Dim Counter As Integer
    Dim arr

    SysCmd acSysCmdSetStatus, "A moment please. Your e-mail message is being sent."
    DoCmd.Hourglass True

    ' Check whether Outlook is active
    On Error Resume Next
    Set objOL = GetObject(, "Outlook.Application")
    blnNotActive = (Err <> 0)

    If blnNotActive Then
    ' If not, we start Outlook
    Err.Clear
    Set objOL = CreateObject("Outlook.Application")
    End If

    On Error GoTo Err_Mail

    ' Create e-mail message
    Set objMI = objOL.CreateItem(olMailItem)
    With objMI
    arr = Split(Recipient, ";")
    For Counter = 0 To UBound(arr)
    Set Recip = objMI.Recipients.Add(arr(Counter))
    Next Counter
    arr = Split(CC, ";")
    For Counter = 0 To UBound(arr)
    Set Recip = objMI.Recipients.Add(arr(Counter))
    Recip.Type = olCC
    Next Counter
    arr = Split(BCC, ";")
    For Counter = 0 To UBound(arr)
    Set Recip = objMI.Recipients.Add(arr(Counter))
    Recip.Type = olBCC
    Next Counter
    .Subject = Subject
    .Body = Message
    .Attachments.Add Attachment, olByValue
    If ShowMail = True Then
    .Display
    Else
    .Send
    End If
    End With
    SendMail = True

    Exit_Mail:
    ' Release object memory
    On Error Resume Next
    If Not (objMI Is Nothing) And Not ShowMail Then
    objMI.Close olDiscard
    End If
    Set objMI = Nothing
    If blnNotActive And Not (objOL Is Nothing) And Not ShowMail Then
    objOL.Quit
    End If
    Set objOL = Nothing
    SysCmd acSysCmdClearStatus
    DoCmd.Hourglass False
    Exit Function

    Err_Mail:
    SendMail = False
    Resume Exit_Mail
    End Function



    It goes through the code as expected, but it prompts the user with "A program is trying to access e-mail addresses you have stored in Outlook. Do you want to allow this?".

    It also is not actually sending the email.

    Is there a way to suppress the prompting of the user?
    Is there anything obviously wrong with the code above?

    Thanks in advance for your consideration and ideas.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Email Attachements (2003)

    See <post:=488,173>post 488,173</post:> for ways to suppress the warning.

    If you supply True for the ShowMail argument, the e-mail will be displayed. Otherwise, it should be sent.

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Email Attachements (2003)

    <hr>It also is not actually sending the email.

    Is there a way to suppress the prompting of the user? <hr>

    Are you answering "yes" to the security message?
    What does happen? Does it create a message and display it in Outlook, but not send it?


    Click Yes is a little utility that will help with the security warning.
    Regards
    John



  7. #7
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Email Attachements (2003)

    Yes, I do respond in the affirmative, and it seems to go through the rest of the code, but no email is created.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Email Attachements (2003)

    Try passing True for the ShowMail argument; the e-mail should be displayed on screen. If that doesn't work, try setting a breakpoint at the beginning of the function, and single-step through it when it's called.

  9. #9
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Email Attachements (2003)

    John and Hans,

    Thanks! I actually set the ShowMail to false, and it works. It still prompts the user, but it does create and send the email with attachment.

    THANKS! ! !

    Ken

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •