Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Email Attachment (2003)

    Hi,

    If I use automation to send email with attachment in access, can user select email address from the company global list instead default email address in the code? Below is the code provided by Hans:

    Function SendMail( _
    Recipient As String, _
    Subject As String, _
    Message As String, _
    Attachment As String) As Boolean

    Dim objOL As Outlook.Application
    Dim objMI As Outlook.MailItem
    Dim blnNotActive As Boolean
    Dim intPos1 As Integer, intPos2 As Integer

    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
    intPos2 = 1
    intPos1 = InStr(intPos2, Recipient, ";")
    Do While intPos1 > 0
    .Recipients.Add Mid$(Recipient, intPos2, intPos1 - intPos2)
    intPos2 = intPos1 + 1
    intPos1 = InStr(intPos2, Recipient, ";")
    Loop
    .Recipients.Add Mid$(Recipient, intPos2)
    .Subject = Subject
    .Body = Message
    .Attachments.Add Attachment, olByValue
    .Send
    End With
    SendMail = True

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

    Err_Mail:
    SendMail = False
    Resume Exit_Mail
    End Function

    Thanks

    Regards

    Thanks

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

    Re: Email Attachment (2003)

    Remove the part that sets the recipient(s) and change the line

    .Send

    to

    .Display

    The e-mail will be displayed on screen, and the user can enter the recipients, then send it.

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

    Re: Email Attachment (2003)

    Hi Hans,

    Thanks! I added a reference to Outlook library. But I don

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

    Re: Email Attachment (2003)

    This function should be copied into a standard module (the kind you create by clicking New in the Modules section of the database window). You can call it - for example - in the On Click event procedure of a command button on a form:

    Private Sub cmdSendMail_Click()
    Dim strFile As String
    strFile = "CocsReport.pdf"
    ' Code to export report to PDF file goes here (its name is in strFile)
    ...
    ' Send e-mail
    Call Function SendMail("you@somewehere.com", "Report", "See the attached report", strFile)
    End Sub

  5. #5
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Email Attachment (2003)

    Gary,
    First, you don't have any code to export the report! <img src=/S/grin.gif border=0 alt=grin width=15 height=15> Where the three ... are, you need to replace them with your export code. Second, you have to put an email address in there. See the thread starting at <post:=211,036>post 211,036</post:> for help.
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

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

    Re: Email Attachment (2003)

    Thanks.

    But I need to use network global list to select name, so I don't have anybody's email address yet. The problem I have is Access don't call funcaiton.

    I need help, please.

    Regards

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

    Re: Email Attachment (2003)

    You should use Call SendMail(...), not Call Function SendMail(...)

    Please don't post pictures larger than 640 (wide) by 480 (high)

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

    Re: Email Attachment (2003)

    Hi Hans,

    Can you help me to look following error message? I don't know why call funcation is not wok?

    Thanks

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

    Re: Email Attachment (2003)

    Image reduced in size by HansV

    Hi,

    Thank you for help again. I try to put the code you provide to me in the command button. But the following error message pops up:

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

    Re: Email Attachment (2003)

    Hi Hans,

    I changed to Call SendMail(...), but still error message pops up. Do I have to change Call SendMail(...) to the following code too?

    Function SendMail( _
    Recipient As String, _
    Subject As String, _
    Message As String, _
    Attachment As String) As Boolean

    Dim objOL As Outlook.Application
    Dim objMI As Outlook.MailItem
    Dim blnNotActive As Boolean
    Dim intPos1 As Integer, intPos2 As Integer

    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
    intPos2 = 1
    intPos1 = InStr(intPos2, Recipient, ";")
    Do While intPos1 > 0
    .Recipients.Add Mid$(Recipient, intPos2, intPos1 - intPos2)
    intPos2 = intPos1 + 1
    intPos1 = InStr(intPos2, Recipient, ";")
    Loop
    .Recipients.Add Mid$(Recipient, intPos2)
    .Subject = Subject
    .Body = Message
    .Attachments.Add Attachment, olByValue
    .Send
    End With
    SendMail = True

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

    Err_Mail:
    SendMail = False
    Resume Exit_Mail
    End Function

    Thanks

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

    Re: Email Attachment (2003)

    Apparently you have a control named SendMail and a function named SendMail. This is confusing. You must rename one of them.

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

    Re: Email Attachment (2003)

    Hi Hans,

    You are correct. After I changed the control name, there's no error message. But nothing appears too. I didn't see any acitivies. What should I do now?

    Thanks

    Regards

  13. #13
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Email Attachment (2003)

    Is the email being sent? You have the command .Send in there. If you want to see something you should use .Display.
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

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

    Re: Email Attachment (2003)

    Hi Hans,

    User need to select email address from our company's global list, so I removed the part that sets the recipient(s) and change the line ".Send" to ".Display" as you told me. Please see below currect code after I changed:

    ' Create e-mail message
    Set objMI = objOL.CreateItem(olMailItem)
    With objMI

    .Subject = Subject
    .Body = Message
    .Attachments.Add Attachment, olByValue
    .Send
    End With
    SendMail = True

    Below is the code of Command button.

    Private Sub Email_Click()
    Dim strFile As String
    strFile = "D:PDFTest.pdf"
    ' Code to export report to PDF file goes here (its name is in strFile)
    ' Send e-mail

    Call SendMail("", "Report", "See the attached report", strFile)
    End Sub

    I clicked this Command button, the following message pops up, I clicked "Yes". Nothing appears. I did check Outlook, no any email was sent.

    I really have no idea what's going on. Please help.... Thanks a lot.

    Regards

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

    Re: Email Attachment (2003)

    The warning you see is caused by Outlook. It proves that your code *does* call Outlook. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    You can download and install the free utility ClickYes to suppress the warning.

    Your code still has .Send instead of .Display.

    If your code really is

    Private Sub Email_Click()
    Dim strFile As String
    strFile = "D:PDFTest.pdf"
    ' Code to export report to PDF file goes here (its name is in strFile)
    ' Send e-mail

    Call SendMail("", "Report", "See the attached report", strFile)
    End Sub

    it doesn't create a file. You were supposed to add code below the line

    ' Code to export report to PDF file goes here (its name is in strFile)

    yourself.

Page 1 of 2 12 LastLast

Posting Permissions

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