CODEWITHSUNDEEP
vbaemail
Donotello
Donotello

Reputation: 157

How to loop sending emails

I have a folder test that contains the following files user1.xlsx , user2.xlsx , user3.xlsx In my working spreadsheet work.xlsx i have corresponding addresses

user1.xlsx  [email protected]
user2.xlsx  [email protected]
 ...

How can I send emails with the attached user1 , user2 .xlsx files to corresponding emails

'Email
Dim OutApp As Object
    Dim OutMail As Object
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    On Error Resume Next
    With OutMail
        .To = userVar
        .SentOnBehalfOfName = "xxxx"
        .CC = ""
        .BCC = ""
        .Subject = "...
        .Body = "...
        .Attachments. .. ??
        .Send
    End With
    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing


'Close

Upvotes: 0

Views: 1076

Answers (1)

filoxo
filoxo

Reputation: 8370

Have you reviewed this MS KB? It details the VBA for sending an email as below:

Sub SendMessage(DisplayMsg As Boolean, Optional AttachmentPath)
      Dim objOutlook As Outlook.Application
      Dim objOutlookMsg As Outlook.MailItem
      Dim objOutlookRecip As Outlook.Recipient
      Dim objOutlookAttach As Outlook.Attachment

      ' Create the Outlook session.
      Set objOutlook = CreateObject("Outlook.Application")

      ' Create the message.
      Set objOutlookMsg  = objOutlook.CreateItem(olMailItem)

      With objOutlookMsg
          ' Add the To recipient(s) to the message.
          Set objOutlookRecip = .Recipients.Add("Nancy Davolio")
          objOutlookRecip.Type = olTo

          ' Add the CC recipient(s) to the message.
          Set objOutlookRecip = .Recipients.Add("Michael Suyama")
          objOutlookRecip.Type = olCC

         ' Add the BCC recipient(s) to the message.
          Set objOutlookRecip = .Recipients.Add("Andrew Fuller")
          objOutlookRecip.Type = olBCC

         ' Set the Subject, Body, and Importance of the message.
         .Subject = "This is an Automation test with Microsoft Outlook"
         .Body = "This is the body of the message." &vbCrLf & vbCrLf
         .Importance = olImportanceHigh  'High importance

         ' Add attachments to the message.
         If Not IsMissing(AttachmentPath) Then
             Set objOutlookAttach = .Attachments.Add(AttachmentPath)
         End If

         ' Resolve each Recipient's name.
         For Each ObjOutlookRecip In .Recipients
             objOutlookRecip.Resolve
         Next

         ' Should we display the message before sending?
         If DisplayMsg Then
             .Display
         Else
             .Save
             .Send
         End If
      End With
      Set objOutlook = Nothing
  End Sub

Notice that you have to Set the objOutlookAttach using the AttachmentPath, which would be the same as the location of your file (hardcode or use current directory as path). Your loop should be for each email address in a specified range, grab the corresponding filename (from adjacent cell), append it to the AttachmentPath variable, and then used to set the objOutlookAttach.

UPDATE: a more up-to-date, related MS article can be found here for additional reference and guidance.

Upvotes: 5

Related Questions