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