Managed to do it, here is the code below on how i got it to work.
Private Sub CommandButton1_Click()
'declare variables
Dim i As Long
Dim lastRow As Long
Dim weekNum As Long
Dim area As String
Dim emailAddr As String
Dim CDO_Mail As Object
Dim CDO_Config As Object
Dim CDO_Fields As Object
Dim ws As Worksheet
'set the worksheet to work with
Set ws = ThisWorkbook.Sheets("Sheet1") 'Change "Sheet1" to the name of your worksheet
'get the week number from cell B12
weekNum = ws.Range("B12").Value
'initialize CDO configuration
Set CDO_Config = CreateObject("CDO.Configuration")
'set the CDO configuration properties
With CDO_Config.Fields
.Item("
http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
.Item("
http://schemas.microsoft.com/cdo/configuration/smtpserver") = "your smtp server" ' smtp server address
.Item("
http://schemas.microsoft.com/cdo/configuration/smtpserverport") = xx ' server port
.Item("
http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
.Update
End With
'loop through the email addresses in the column that corresponds to the week number
lastRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
For i = 14 To lastRow
'get the area and email address
area = ws.Range("A" & i).Value
emailAddr = ws.Cells(i, weekNum + 1).Value
'create a new email
Set CDO_Mail = CreateObject("CDO.Message")
'set the email properties
With CDO_Mail
.Subject = "Subject of the email"
.From = "
noreply@mail.com" 'replace with your email address
.To = emailAddr
.TextBody = "Dear " & emailAddr & "," & vbCrLf & vbCrLf & _
"This is the body of the email. " & area & "." & vbCrLf & vbCrLf & _
"Regards," & vbCrLf & "Your Name"
.Configuration = CDO_Config
.Send
End With
'release the memory
Set CDO_Mail = Nothing
Next i
'release the memory
Set CDO_Config = Nothing
End Sub
