1

I created a macro to cycle through a range of cells in a column (G2:G5), paste that value into another cell (A2), look up details to create an e-mail with attachments (To email address, Cc email addresses, Recipient First Name, File attachment names).

The to and cc recipients are being accumulated rather than replaced.

For example, rather than [Email 2 To: Email 2 recipient, CC: Email 2 recipients]

I get [Email 2 To: Email 1 & 2 recipient, CC: Email 1 & 2 recipient), so on and so forth until my last email has all recipients.

I assume this has something to do with the For/next loop with the to/cc recipients and/or the to/cc recipient variables.

Sub create_email()

Dim cell As Range
Dim selectedRange As Range

Sheets("Email Generator").Select
Range("G2:G5").Select

Set selectedRange = Application.Selection

For Each cell In selectedRange.Cells

    cell.Copy
    Range("A2").Select
    ActiveSheet.Paste

    Dim outlookApp As Outlook.Application
    Dim myMail As Outlook.MailItem
    Dim source_file, to_emails, cc_emails As String
    Dim i, j As Integer

    Set outlookApp = New Outlook.Application
    Set myMail = outlookApp.CreateItem(olMailItem)

    For i = 2 To 2
        to_emails = to_emails & Cells(i, 1) & ";"
        cc_emails = cc_emails & Cells(i, 2) & ";"
    Next i

    For j = 2 To Range("A8").Value
        source_file = "C:\Gavin Files\General\" & Cells(j, 3)
        myMail.Attachments.Add source_file

        myMail.CC = cc_emails
        myMail.To = to_emails
        myMail.Subject = "Rewards Statements - Please Communicate by 1/11"
        myMail.HTMLBody = Range("A5").Value & "," & "<br>" & "<br>" & "All year-end rewards are approved and the next step is formal communication to your employees." & "<br>" & "<br>" & "Attached are compensation statements to be delivered to your employees during rewards discussions next week.  Please communicate by Friday, January 10th.  As a reminder, performance discussions must occur prior to the rewards discussion." & "<br>" & "<br>" & " - Jan 6-10 - Managers hold rewards communications with their direct reports" & "<br>" & " - Jan 13 - Increases visible to all employees in Workforce Now" & "<br>" & " - Jan 17 - Merit and bonus effective in payroll (promotions and increases effective Jan 6)" & "<br>" & "<br>" & "Thank you for your leadership and commitment to the compensation planning process. Please let us know if you have any questions." & "<br>" & "<br>" & "Thank you," & "<br>" & "Gavin"
        myMail.Display
        myMail.Save
    Next j

Next cell

End Sub
Community
  • 1
  • 1
Gavin
  • 21
  • 3
  • How are you distinguishing the `TO` and `CC` email recipients ? – stud3nt Jan 04 '20 at 02:58
  • [How to avoid using Select in Excel VBA](https://stackoverflow.com/q/10714251/4539709) – 0m3r Jan 04 '20 at 03:29
  • That was it! I just dropped those unnecessary to/cc variable declarations and referred directly to the sheet cell values in the mail.cc/to fields below. Now it’s working beautifully - and it’s an amazing sight to behold! So much time saved! – Gavin Jan 04 '20 at 03:58

0 Answers0