0

I am getting the error Expected end of statement

Option Explicit On
Option Strict On

Sub CreateTableAndSendEmails()
    Dim ws As Worksheet
    Dim rng As Range
    Dim lastRow As Long
    Dim emailDict As Object
    Dim cell As Range
    Dim email As String
    
    ' Set the worksheet where the data is located
    Set ws = ThisWorkbook.Sheets("vendor_account") ' Modify the sheet name as per your requirement
    
    ' Set the range where the email addresses are located
    Set rng = ws.Range("W14:W33") ' Modify the range as per your requirement
    
    ' Create a dictionary to store unique email addresses and their corresponding rows
    Set emailDict = CreateObject("Scripting.Dictionary")
    
    ' Get the last used row in the range
    lastRow = rng.Cells(rng.Cells.Count).Row + rng.Cells.Count - 1
    
    ' Loop through each cell in the range and populate the dictionary
    For Each cell In rng
        If cell.Value <> "" And InStr(cell.Value, "@") > 0 Then
            email = cell.Value
            
            ' Check if the email address already exists in the dictionary
            If Not emailDict.exists(email) Then
                ' Add the email address and the corresponding row to the dictionary
                emailDict(email) = ws.Range("A" & cell.Row & ":V" & cell.Row).Value ' Modify the range (A:V) as per your requirement
            Else
                ' If the email address already exists, append the additional row data to the existing entry
                emailDict(email) = Application.WorksheetFunction.Transpose(emailDict(email)) & Application.WorksheetFunction.Transpose(ws.Range("A" & cell.Row & ":V" & cell.Row).Value)
            End If
        End If
    Next cell
    
    ' Loop through each unique email address in the dictionary and send an email
    For Each email In emailDict.keys
        ' Create a new Outlook email
        Dim OutApp As Object
        Dim OutMail As Object
        
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
        
        ' Compose the email
        With OutMail
            ' Set the email recipient
            .To = email
            
            ' Set the subject of the email
            .Subject = "Regarding the data in the table"
            
            ' Set the body of the email
            .Body = "Dear recipient," & vbCrLf & vbCrLf & _
                    "Please find the information below:" & vbCrLf & vbCrLf & _
                    "Column A: " & emailDict(email)(1, 1) & vbCrLf & _
                    "Column V: " & emailDict(email)(1, 2) & vbCrLf & _
"Column K: " & emailDict(email)(1, 3) & vbCrLf & _
                    "Column L: " & emailDict(email)(1, 4) & vbCrLf & vbCrLf & _
                    "Best regards," & vbCrLf & "Your Name"
            
            ' Send the email
            '.Send
.Display
        End With
        
        ' Release the Outlook objects
        Set OutMail = Nothing
        Set OutApp = Nothing
    Next email
    
    ' Inform the user that the emails have been sent
    MsgBox "Emails have been sent successfully!", vbInformation
End Sub

Before I included Option Explicit On & Option Strict On I got the error Compile Error: For Each Control Variable must me Variant or Object regarding the line For Each email In emailDict.keys in my code

BigBen
  • 46,229
  • 7
  • 24
  • 40
  • Declare `email`as `variant` – Ike May 26 '23 at 11:53
  • 1
    You're confusing VB.Net and VBA. It's `Option Explicit`, no `On`. There's no `Option Strict` in VBA. – BigBen May 26 '23 at 12:47
  • You fell into your own trap with an unfocused question. Although `Option Explicit` should be considered mandatory it does not address the `For Each` error. Since email **address** is text use another type of loop. – niton May 30 '23 at 13:32

0 Answers0