Results 1 to 6 of 6
  1. #1
    dj59 is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Apr 2015
    Posts
    13

    Attach file to email using an array of emails and an IF/Then statement

    I want to use an array of email addresses and for each address in the array attach a different file.
    The following code works okay the first time through, but the second time through it attaches both files and inserts the first two emails in the array.


    I think I need something after the IF statement to clear everything for the second loop through, but I don't know what to use to clear.

    Code:
    Private Sub cmdSendIt_Click()
    'send outlook email .zip attachments.
    Dim OlApp As Object
    Dim OlMail As Object
    Dim ToRecipient As Variant
    Dim CcRecipient As Variant
    Dim i As Integer
    Dim x As Variant
    Set OlApp = CreateObject("Outlook.Application")
    Set OlMail = OlApp.createitem(olmailitem)
    ToRecipient = Array("Jon.Doe@gmail.com", "Jane.Doe@yahoo.com", "Mary.Smith@hotmail.com")
            For i = 0 To 1
            x = ToRecipient(i)
      
    'add email address from array
    OlMail.Recipients.Add x
    'fill in Subject field
    OlMail.Subject = "TEST Auto email with attachment
    'body test>
    OlMail.Body = "This is to test the automatic sending of reports." 
    If i = 0 Then
    'Add the .zip file as an attachments
    OlMail.Attachments.Add "C:\Test\01.zip"
    ElseIf i = 1 Then
    OlMail.Attachments.Add "C:\Test\02.zip"
    Else: MsgBox "Check it"
    End If
    'Display the message
    OlMail.Display 'change this to OlMail.Send if you just want to send it without previewing it
    
    ' clear everything here......I think.
    
            Next i
    End Sub

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,028
    Always send to the same 3 email addresses?

    Don't use the Recipients.Add property. Just use .To.

    Now clearing the attachment element not something I ever had to do but review http://stackoverflow.com/questions/2...-the-next-loop
    Try .DeleteAll as described in that reference.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    dj59 is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Apr 2015
    Posts
    13
    If I use:
    Code:
    OlMail.To
    I get the runtime errore 440 - The object does not support this method

    If I use:
    Code:
    .To
    I get compile error - Invalid or unqualified reference.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,028
    Odd, works for me.

    Then maybe try the .DeleteAll on the Recipients.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    dj59 is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Apr 2015
    Posts
    13
    Update:
    The following code works well all the way through the first loop, but the second time through I get an error:
    "Runtime error 91: Object variable or with block variable not set. At line OlMail.Recipients.Add x
    Code:
    Private Sub cmdSendIt_Click()
    'send outlook email .zip attachments.
    Dim OlApp As Object
    Dim OlMail As Object
    Dim ToRecipient As Variant
    Dim CcRecipient As Variant
    Dim i As Integer
    Dim x As Variant
    Set OlApp = CreateObject("Outlook.Application")
    Set OlMail = OlApp.createitem(olmailitem)
    ToRecipient = Array("Jon.Doe@gmail.com", "Jane.Doe@yahoo.com", "Mary.Smith@hotmail.com")
    For i = 0 To 2
    x = ToRecipient(i)
    'add email address from array
    OlMail.Recipients.Add x
    'fill in Subject field
    OlMail.Subject = "TEST Auto email with attachment
    'body test>
    OlMail.Body = "This is to test the automatic sending of reports." 
    If i = 0 Then
    'Add the .zip file as an attachments
    OlMail.Attachments.Add "C:\Test\01.zip"
    ElseIf i = 1 Then
    OlMail.Attachments.Add "C:\Test\02.zip"
    Else: MsgBox "Check it"
    End If
    'Display the message
    OlMail.Display 'change this to OlMail.Send if you just want to send it without previewing it
    'Set olNs = Nothing
    Set OlMail = Nothing
    Set OlApp = Nothing
    'Set olItem = Nothing
    Set OlApp = Nothing
    Next i
    End Sub

  6. #6
    dj59 is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Apr 2015
    Posts
    13
    Found the Answer! I needed to Set OlApp and OlMail within the For statement as follows:
    Code:
            For i = 0 To 2
            x = ToRecipient(i)
    Set OlApp = CreateObject("Outlook.Application")
    Set OlMail = OlApp.CreateItem(olMailItem)

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 09-05-2014, 12:06 PM
  2. Attach Database to Email
    By SSgtBarry in forum Access
    Replies: 2
    Last Post: 08-10-2014, 08:42 PM
  3. Attach Report to Email
    By teebumble in forum Reports
    Replies: 5
    Last Post: 11-28-2012, 05:42 PM
  4. Can't attach to email
    By newtoAccess in forum Access
    Replies: 10
    Last Post: 12-03-2010, 02:10 PM
  5. Attach Email to Form
    By Huddle in forum Access
    Replies: 2
    Last Post: 07-22-2010, 12:39 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums