How to paste a selected range in Excel to an Outlook email using VBA? (into the body of the email)

[flower3954]

Hi all-

I am trying to fully automate the creation of emails. See VBA code below.

I just need to use VBA to paste the selected range from excel into the body of the Outlook email.

Any suggestions?

Sub WELCOMEEMAIL_Button19_Click()
Dim OutApp As Object
Dim OutMail As Object
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
' Change the mail address and subject in the macro before you run it.
With OutMail
.To = ""
.CC = Cells(1, 1).Text
.BCC = "STIP.CHASER@HUNTINGTON.COM"
.Subject = ""
.Subject = "A MESSAGE FROM HUNTINGTON - YOU'VE REACHED THE NEXT STEP IN THE LOAN PROCESS - " & Cells(2, 1).Text
.Body = ""
.Display
.Attachments.Add "http://my.hban.us/personal/hb10410/Shared Documents/BORROWER AUTH FORM.pdf"
Range("b3:b50").Select
Selection.Copy

End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

 

[scott_n_phnx]

I added a couple lines to your code. Specifically

Dim Email_Body As String
Dim b1 As Variant
b1 = Range("K1")
Email_Body = b1

.Body = Email_Body

Sub WELCOMEEMAIL_Button19_Click()
Dim OutApp As Object
Dim OutMail As Object
Dim Email_Body As String
Dim b1 As Variant
b1 = Range("K1")
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
Email_Body = b1
On Error Resume Next
' Change the mail address and subject in the macro before you run it.
With OutMail
.To = ""
.CC = Cells(1, 1).Text
.BCC = "STIP.CHASER@HUNTINGTON.COM"
.Subject = ""
.Subject = "A MESSAGE FROM HUNTINGTON - YOU'VE REACHED THE NEXT STEP IN THE LOAN PROCESS - " & Cells(2, 1).Text
.Body = Email_Body
.Display
.Attachments.Add "http://my.hban.us/personal/hb10410/S...UTH%20FORM.pdf"
Range("b3:b50").Select
Selection.Copy

End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

 

[flower3954]

The macros is not working...I have the code written as...

Sub WELCOMEEMAIL_Button19_Click()
Dim OutApp As Object
Dim OutMail As Object
Dim Email_Body As String
Dim b1 As Variant
b1 = Range("b3:b50")
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
Email_Body = b1
On Error Resume Next
' Change the mail address and subject in the macro before you run it.
With OutMail
.To = ""
.CC = Cells(1, 1).Text
.BCC = "STIP.CHASER@HUNTINGTON.COM"
.Subject = ""
.Subject = "A MESSAGE FROM HUNTINGTON " & Cells(2, 1).Text
.Body = Email_Body
.Display
.Attachments.Add "http://my.hban.us/personal/hb10410/S...UTH FORM.pdf"
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

I need Range("b3:b50") to be copied and pasted into the body of the email.

I'm getting a runtime error...with the line:

Email_Body = b1

...being shown as the error point.

Thoughts?

Thanks!

I added a couple lines to your code. Specifically

Dim Email_Body As String
Dim b1 As Variant
b1 = Range("K1")
Email_Body = b1

.Body = Email_Body

Sub WELCOMEEMAIL_Button19_Click()
Dim OutApp As Object
Dim OutMail As Object
Dim Email_Body As String
Dim b1 As Variant
b1 = Range("K1")
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
Email_Body = b1
On Error Resume Next
' Change the mail address and subject in the macro before you run it.
With OutMail
.To = ""
.CC = Cells(1, 1).Text
.BCC = "STIP.CHASER@HUNTINGTON.COM"
.Subject = ""
.Subject = "A MESSAGE FROM HUNTINGTON - YOU'VE REACHED THE NEXT STEP IN THE LOAN PROCESS - " & Cells(2, 1).Text
.Body = Email_Body
.Display
.Attachments.Add "http://my.hban.us/personal/hb10410/S...UTH%20FORM.pdf"
Range("b3:b50").Select
Selection.Copy

End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

 

[scott_n_phnx]

Your range, is it a merged range, or there are 47 cell values that you need to add into the body?

 

[flower3954]

The range includes merged cells....it's a standardized email with data point from a different tab.

Your range, is it a merged range, or there are 47 cell values that you need to add into the body?

 

[Angie28]

hello. how did you do in the end? i need the same code.. as this vba code, but this code below doesnt work. thank you.

 

[flower3954]

hello. how did you do in the end? i need the same code.. as this vba code, but this code below doesnt work. thank you.

Sorry to say I never figured it out. After the range is copied I can get an email to open, populate subject line and recipients...but the formatted range copied to the clipboard has to be pasted manually with CTRL V.

The code below will do the following:

1) Open an Outlook email
2) Populate Subject Line
3) Populate Recipients
4) Add an attachment if appropriate
5) Copy Range to clipboard

On Error Resume Next
Application.ScreenUpdating = False
Sheets("email").Unprotect Password:=""

Sheets("email").Select
Dim OutApp As Object
Dim outmail As Object
Set OutApp = CreateObject("Outlook.Application")
Set outmail = OutApp.CreateItem(0)
On Error Resume Next

.To = Range("a1").Text
.CC = ""
.BCC = ""
.Subject = "WELCOME! FOLLOW UP FROM HUNTINGTON - " & Range("b1").Text
.Body = ""
.Display

If Range("attach_app").Value = "1" Then
.Attachments.Add "http://team.hb.us/wgh/84ec35ca/APPLICATION%20PURCHASE.pdf"
End If

Sheets("email").Select
Sheets("email").Protect Password:=""

End With
On Error GoTo 0
Set outmail = Nothing
Set OutApp = Nothing

Sheets("email").Protect Password:=""
Range("e7:be85").Select
Selection.Copy