VBAstudent1986
New Member
- Joined
- Jan 8, 2020
- Messages
- 16
- Office Version
- 2016
- Platform
- Windows
IN the macro below, I am looking to fill in cell values (marked XXXX below) into the body of the email.
Need help with the cell referencing.
Each XXXX should refer to a particular value in the excel sheet. Any help would be great!!
Sub send_Email()
Dim i As Long, j As Long, k As Long
i = 6
' k = 3
'j = 5
Do While i <= 6
Dim outlookOBJ As Object
Dim mItem As Object
Dim strbody As String
Set outlookOBJ = CreateObject("outlook.application")
Set mItem = outlookOBJ.CreateItem(olmailItem)
On Error Resume Next
strbody = "<BODY style = font-size:12pt; font-family:Arial>" & _
"Dear XXXX,<P>" & vbNewLine & vbNewLine & _
"Thank you for being a valued XXXX customer. <P> " & vbNewLine & vbNewLine & _
"We have discovered that your XXXX vehicle was incorrectly registered Please correct it from your end & _
With mItem
.sentonbehalfofname = Sheets("Data").Cells(i, "M").Value
.To = Sheets("Email").Cells(i, "F").Value
' .cc = Sheets("Email").Cells(i, "I").Value
' .bcc = Sheets("Email").Cells(i, "f").Value
.Subject = "Vehicle was incorrectly registered"
.Display
.HTMLBody = strbody & _
"<img src='D:\Nov 2020\MMFS\mmfslogo.jpg' height='20%' > " & _
.HTMLBody
.send
Sheets("Email").Cells(i, "N").Value = "Email Sent"
End With
' Sheets("Email").Cells(i, "N").Value = "Missing attachments"
i = i + 1
j = j + 1
k = k + 1
Loop
MsgBox " Email macro is complete"
End Sub
Need help with the cell referencing.
Each XXXX should refer to a particular value in the excel sheet. Any help would be great!!
Sub send_Email()
Dim i As Long, j As Long, k As Long
i = 6
' k = 3
'j = 5
Do While i <= 6
Dim outlookOBJ As Object
Dim mItem As Object
Dim strbody As String
Set outlookOBJ = CreateObject("outlook.application")
Set mItem = outlookOBJ.CreateItem(olmailItem)
On Error Resume Next
strbody = "<BODY style = font-size:12pt; font-family:Arial>" & _
"Dear XXXX,<P>" & vbNewLine & vbNewLine & _
"Thank you for being a valued XXXX customer. <P> " & vbNewLine & vbNewLine & _
"We have discovered that your XXXX vehicle was incorrectly registered Please correct it from your end & _
With mItem
.sentonbehalfofname = Sheets("Data").Cells(i, "M").Value
.To = Sheets("Email").Cells(i, "F").Value
' .cc = Sheets("Email").Cells(i, "I").Value
' .bcc = Sheets("Email").Cells(i, "f").Value
.Subject = "Vehicle was incorrectly registered"
.Display
.HTMLBody = strbody & _
"<img src='D:\Nov 2020\MMFS\mmfslogo.jpg' height='20%' > " & _
.HTMLBody
.send
Sheets("Email").Cells(i, "N").Value = "Email Sent"
End With
' Sheets("Email").Cells(i, "N").Value = "Missing attachments"
i = i + 1
j = j + 1
k = k + 1
Loop
MsgBox " Email macro is complete"
End Sub
