Sending emails using VBA, getting the email addresses and body from cell ranges

Hoareman

New Member
Joined
Jun 3, 2021
Messages
5
Office Version
  1. 2019
Platform
  1. Windows
  2. MacOS
Hi,

I am fairly new to VBA and I am trying to set up auto sending emails using excel.

I have an example of the data below (totally fictitious of course).

1622734574152.png


I need a code to be able to send emails to the recipients in column E.
The body of the email should be roughly

'Dear (Account holder),
Please see below your account number, type and opening date,

Account No: (column A)
Account Type (column C)
Open Date: (column B).

Kind regards,

(Sign email from)' (or if the person sending the email from outlook has a signature enabled, this can be used instead)

Each email will have the same subject line and the same attachment.

My VBA skills are not yet up to writing a code to enable this.
I can manage to set up email going to different addresses, however, I have not been able to include all the info from other cells that I need.

Any help with this would be greatly appreciated.

Thanks
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

sxhall

Board Regular
Joined
Jan 5, 2005
Messages
150
Hi,

It sounds like you have the VBA to form the e-mail but are just having an issuing in looping through and creating the '.Body' part of the e-mail from multiple cells.

The code below loops through each row in my mocked up data and forms it in to one string called MailBody. This comprises of the greeting, persons name etc through to the sign off name. The vbCr you see is just adding the carriage return to format it with a row between lines.

The part MyName in my example takes my system name and puts my surname last and forename first as they are flipped in my Excel setup. I use this at the end of my string MailBody to add in the senders name.

For the e-mail address I have used SendTo and capture this from the data to use in the '.To' part of the e-mail,

VBA Code:
Sub EmailBody()

Dim SendTo As String
Dim Myname As String
Dim MailBody As String

    Myname = Application.UserName
    Myname = Right(Myname, Len(Myname) - InStr(Myname, " ")) & " " & Left(Myname, InStr(Myname, ", ") - 1) ' ** Sorts name in to first name first and then surname **

For Each i In Range("A2:A4")

 '** creates the text to go in to the e-mail **
MailBody = i.Value & " " & i.Offset(0, 1) & vbCr & vbCr & i.Offset(0, 2) & vbCr & vbCr & i.Offset(0, 3) & vbCr & vbCr & i.Offset(0, 4) & vbCr & vbCr & Myname
SendTo = i.Offset(0, 5).Value

MsgBox MailBody & vbCr & vbCr & SendTo   '** remove this as it is only there for proofing purposes **

'** Your e-mail code here with .Body = EmailBody, rough example below but not he full code.
'    With OutMail
'        .To = SendTo
'        .BCC = ""
'        .Subject = "Title of e-mail"
'        .Body = EmailBody
'        .Display
'    End With

Next i

End Sub

You woud need to add in the code you have for the e-mail where indicated above and type EmailBody for the 'Body.' part in your code and SendTo for the '.To' part.

The popup is what the above generates for row 2 in my data...

1622766314214.png


Steven
 

Hoareman

New Member
Joined
Jun 3, 2021
Messages
5
Office Version
  1. 2019
Platform
  1. Windows
  2. MacOS
Hi,

It sounds like you have the VBA to form the e-mail but are just having an issuing in looping through and creating the '.Body' part of the e-mail from multiple cells.

The code below loops through each row in my mocked up data and forms it in to one string called MailBody. This comprises of the greeting, persons name etc through to the sign off name. The vbCr you see is just adding the carriage return to format it with a row between lines.

The part MyName in my example takes my system name and puts my surname last and forename first as they are flipped in my Excel setup. I use this at the end of my string MailBody to add in the senders name.

For the e-mail address I have used SendTo and capture this from the data to use in the '.To' part of the e-mail,

VBA Code:
Sub EmailBody()

Dim SendTo As String
Dim Myname As String
Dim MailBody As String

    Myname = Application.UserName
    Myname = Right(Myname, Len(Myname) - InStr(Myname, " ")) & " " & Left(Myname, InStr(Myname, ", ") - 1) ' ** Sorts name in to first name first and then surname **

For Each i In Range("A2:A4")

 '** creates the text to go in to the e-mail **
MailBody = i.Value & " " & i.Offset(0, 1) & vbCr & vbCr & i.Offset(0, 2) & vbCr & vbCr & i.Offset(0, 3) & vbCr & vbCr & i.Offset(0, 4) & vbCr & vbCr & Myname
SendTo = i.Offset(0, 5).Value

MsgBox MailBody & vbCr & vbCr & SendTo   '** remove this as it is only there for proofing purposes **

'** Your e-mail code here with .Body = EmailBody, rough example below but not he full code.
'    With OutMail
'        .To = SendTo
'        .BCC = ""
'        .Subject = "Title of e-mail"
'        .Body = EmailBody
'        .Display
'    End With

Next i

End Sub

You woud need to add in the code you have for the e-mail where indicated above and type EmailBody for the 'Body.' part in your code and SendTo for the '.To' part.

The popup is what the above generates for row 2 in my data...

View attachment 40023

Steven

Hi Steven,

Thanks for the previous help.
I took the code you have above and slotted it into the code I had. It manages to produce the string of emails, however it puts the info from the top line into every single email, not splitting the info into separate emails.

I have included a mini sheet with my data, and the VBA code I am using underneath.

Any ideas?

Book1 with macros.xlsm
ABCDL
1Hospital NumberExam DateExamRadiographerReporting Rad checking image
2R0015/13/21AnkleJames HoareJames Hoare
3R0025/13/21PelvisHarry SmythAnna O'Brien
4R0035/13/21FootJames HoareSteph Patts
Sheet1
Cells with Data Validation
CellAllowCriteria
D1:D4List='Reference sheet'!$A$2:$A$331
L1:L4List='Reference sheet'!$E$2:$E$22
\

Sub ImagesForQaEmails()
Application.ScreenUpdating = False

ThisWorkbook.Activate
'Creating references to Application and MailItem Objects of Outlook
Dim outApp As Outlook.Application
Dim outMail As Outlook.MailItem

'Creating variable to hold values of different items of mail
Dim sendTo, MailBody As String

Dim lstRow As Long

ThisWorkbook.Sheets("Sheet1").Activate
'Getting last row of containing email id in column 3.
lstRow = Cells(Rows.Count, 5).End(xlUp).Row

Dim rng As Range
Set rng = Range("E2:E" & lstRow)

'initializing outlook object to access its features
Set outApp = New Outlook.Application
On Error GoTo cleanup 'to handle any error during creation of object.

For Each i In Range("A2:A100")

MailBody = "Dear " & i.Offset(0, 5) & "," & vbCr & vbCr & i.Offset(0, 6) & vbCr & vbCr & i.Offset(0, 7) & vbCr & vbCr & i.Offset(0, 8) & vbCr & vbCr & "Hospital Number: " & i.Value & vbCr & "Exam Date: " & i.Offset(0, 1) & vbCr & "Exam: " & i.Offset(0, 2) & vbCr & vbCr & "Kind regards," & vbCr & vbCr & i.Offset(0, 11) & vbCr & i.Offset(0, 9) & vbCr & i.Offset(0, 10)

For Each cell In rng
sendTo = Range(cell.Address).Offset(0, 0).Value2
subj = "Images for QA"
msg = MailBody

On Error Resume Next 'to hand any error during creation of below object
Set outMail = outApp.CreateItem(0)

'Writing and sending mail in new mail
With outMail
.To = sendTo
.Body = msg
.Subject = subj
.Display

End With
On Error GoTo 0 'To clean any error captured earlier
Set outMail = Nothing 'nullifying outmail object for next mail
Next cell 'loop ends

cleanup: 'freeing all objects created
Set outApp = Nothing
Application.ScreenUpdating = True
Application.ScreenUpdating = True

Next i

End Sub
 

Forum statistics

Threads
1,141,611
Messages
5,707,383
Members
421,508
Latest member
Jalayne

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
Top