Paste Excel range into outlook email body VBA

ollieb34

New Member
Joined
Jan 27, 2021
Messages
32
Office Version
  1. 2016
Platform
  1. Windows
Good Morning.

I am running a Macro which Is exporting a range to pdf and attaching it to an email.

Id like to add a summarise the PDF by copying a specific range of cells into the body of the email which has been prepared.

I have already wasted far too much time researching this and trying to modify my original code with no joy..

Any help would be greatly appreciated

Thanks in advance

Code below which needs modifying to copy the range:

Range which needs copying to outlook is in (sheet 1 range Y4:Z24)

Sub Send_Email()
Dim wPath As String, wFile As String

wPath = ThisWorkbook.Path
wFile = "Filepdf.pdf"

Sheets("Production Scheduler").Activate
ActiveSheet.Select

ActiveSheet.Range("c2:HO86").ExportAsFixedFormat Type:=xlTypePDF, Filename:=wPath & wFile, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=False


Set dam = CreateObject("Outlook.Application").CreateItem(0)
'
dam.To = "email@email"
dam.Subject = "**3 Hourly Update**"
dam.Body = "Hi, See attached Update"
dam.Attachments.Add wPath & wFile
dam.Send
MsgBox "Email sent"


End Sub
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Are you getting an error? If so, on which line and which error?

Actually, it looks like you're missing a backslash ( \ ) between the path and filename when referencing the path and filename of your PDF file. So it should be . . .

VBA Code:
ActiveSheet.Range("c2:HO86").ExportAsFixedFormat Type:=xlTypePDF, Filename:=wPath & "\" & wFile, _

and

VBA Code:
dam.Attachments.Add wPath & "\" & wFile

Does this help?
 
Upvote 0
Are you getting an error? If so, on which line and which error?

Actually, it looks like you're missing a backslash ( \ ) between the path and filename when referencing the path and filename of your PDF file. So it should be . . .

VBA Code:
ActiveSheet.Range("c2:HO86").ExportAsFixedFormat Type:=xlTypePDF, Filename:=wPath & "\" & wFile, _

and

VBA Code:
dam.Attachments.Add wPath & "\" & wFile

Does this help?
Actually Iv just resolved it. My previous code was using .dam to attach the exported pdf within outlook.

Thanks for your quick response. I did add the backslash as you mentioned.

Thanks for your help
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,106
Members
452,302
Latest member
TaMere

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