Need to have a custom body of the email via VBA

V

VBAstudent1986

New Member
Joined
Jan 8, 2020
Messages
16
Office Version
  1. 2016
Platform
  1. Windows
IN the macro below, I am looking to fill in cell values into the body of the email.

Need help with the cell referencing.


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 = "Mitsubishi 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
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
VBAstudent1986 said:
IN the macro below, I am looking to fill in cell values into the body of the email.

Need help with the cell referencing.


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 = "Mitsubishi 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
Click to expand...
Where exactly do you have an issue and what range do you intend to insert? and where
 
Upvote 0
Thanks for the response.. I need to insert different names on the body of the email. For example, I should be able to say "Dear John" in one email and "Dear Harry" in another. The names John, Harry etc will be in the excel sheet. Since the script here is on a Do while loop, i thought it wd be possible to reference the cells on the body of the email but I am unable to.

Any help please
 
Upvote 0
For example
VBA Code: 
.Body = "Dear " & Cells(i, 1) & "," & vbLf & vbLf & "Regards, Victor"
 
Upvote 0
Solution
Hi Momentman

looking to change the font to Arial (for eg). what is the code that i have to input in the .Body
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
How to VBA things for weekly email
Replies
1
Views
424
Domenic
D
The Ruff Report
  • Question
Help adjusting Macro to email multiple cc addresses
Replies
1
Views
432
The Ruff Report
The Ruff Report
The Ruff Report
  • Question
Add multiple attachments from email list
Replies
1
Views
323
Trebor76
T
A
  • Solved
Sending hyperlink filepath via email
Replies
8
Views
425
AndyEd
A
G
  • Solved
Dynamic Button for Email Macro
Replies
4
Views
255
GarnesGambit
G

Forum statistics

Threads
1,215,773
Messages
6,126,821
Members
449,340
Latest member
hpm23

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top