0

I am attempting to write a Microsoft VBA macro to automate bulk emails to a distribution list that is entered into a column within a specific sheet of the active workbook.

I am attempting to use a for loop to get the email addresses from the column and then send the same subject and body to each of the addresses during the loop.

I am using a counter variable that is comprised of the number of cells containing email addresses in the sheet with the distribution list.

When I attempt to interpolate the iterator variable into this generic statement: Range($"Sheet!B{I}").Value There is a "Compiler Error: Syntax Error"

I am creating this macro and workbook for a friend and am hoping that I can fix this error in a way that will still work no matter where it is being utilized.

Sub SEND_EMAIL_FROM_EXCEL()
    ' Sends an email with attachments using email addresses in the current workbook

    Dim emailApplication As Object
    Dim emailItem As Object
    Dim counter, I As Long

    Set emailApplication = CreateObject("Outlook.Application")
    Set emailItem = emailApplication.CreateItem(0)

    ' Send the email using input from the specified column for email addresses
    emailItem.Subject = Range("Content!B1").Value
    emailItem.Body = Range("Content!B2").Value

    ' Note:  Attempting to use the following command to add an attachment currently causes an error
    ' emailItem.Attachments.Add = Range("Content!B3").Value

    counter = Range("To!L1").Value
    For I = 1 To counter Step 1
        ' Note: Attempting to interpolate the loop variable into the string currently causes an error
        emailItem.To = Range($"To!B{I}").Value
        emailItem.Send
        ' Display email to sender prior to sending, for testing
        ' emailItem.Display
    Next I

    Set emailItem = Nothing
    Set emailApplication = Nothing


End Sub

Microsoft VBA Error

BigBen
  • 46,229
  • 7
  • 24
  • 40
Anthem Wingate
  • 1
  • 1
  • Variables need concatenated with `&`. `emailItem.To = ActiveWorkbook.Worksheets("To").Range("B" & I).Value`. VBA doesn't have string interpolation. – BigBen Oct 20 '22 at 20:32
  • Alternately, you can use `Replace` with placeholder(s). – BigBen Oct 20 '22 at 20:42
  • Thank you @BigBen. Using your instruction, I tried to run with that change but I get the following error: "Run-time error '1004': Application-defined or object-defined error" – Anthem Wingate Oct 20 '22 at 21:11
  • You could try `Range("To!B" & I).Value` but it would be better IMO to use `Worksheets`. – BigBen Oct 20 '22 at 21:17

0 Answers0