Send email

[lizemi]

I have the following VB code that works perfectly but I need to change the format it sends it from Pdf to an Excel sheet I have tried to change all the Pdf in the code to Xls . It does send the file as an excel sheet then but the recipients can’t open it as it says the file does not exist


Sub Send_Email()

Dim wPath As String, wFile As String



wPath = ThisWorkbook.Path

wFile = "Filepdf.Pdf"

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=wPath & wFile, _

Quality:=xlQualityStandard, IncludeDocProperties:=True, _

IgnorePrintAreas:=False, OpenAfterPublish:=False





Set dam = CreateObject("Outlook.Application").CreateItem(0)

'

dam.To = Range("a2")

dam.Subject = Range("A1")

dam.Body = "Regards"

dam.Attachments.Add wPath & wFile

dam.Send

MsgBox "Email sent"





End Sub

 

[HaHoBe]

Hi lizemi,

copy the active sheet and save the new workbook. Code may look like this:

Sub Send_Email()
'https://www.mrexcel.com/board/threads/send-email.1213385/

Dim wPath As String, wFile As String

Const cblnDelFile As Boolean = False    'change to True for wiping the file from Disk
                                        'False will leave the file in place untouched

wPath = ThisWorkbook.Path & Application.PathSeparator     'adds \ to path on Windows
wFile = "File" & Format(Now, "_yymmdd_hhnnss_") & "xls.xls"

ActiveSheet.Copy
ActiveWorkbook.SaveAs wPath & wFile, FileFormat:=56

'Explanation from https://www.rondebruin.nl/win/s5/win001.htm
'These are the main file formats in Excel 2007-2016, Note: In Excel for the Mac the values are +1
'
'51 = xlOpenXMLWorkbook (without macro's in 2007-2016, xlsx)
'52 = xlOpenXMLWorkbookMacroEnabled (with or without macro's in 2007-2016, xlsm)
'50 = xlExcel12 (Excel Binary Workbook in 2007-2016 with or without macro's, xlsb)
'56 = xlExcel8 (97-2003 format in Excel 2007-2016, xls)
'
'Note: I always use the FileFormat numbers instead of the defined constants in my code
'      so that it will compile OK when I copy the code into an Excel 97-2003 workbook
'      (For example, Excel 97-2003 won't know what the xlOpenXMLWorkbookMacroEnabled constant is).
'End of Explanation

ActiveWorkbook.Close True

Set dam = CreateObject("Outlook.Application").CreateItem(0)
'
dam.To = Range("a2")
dam.Subject = Range("A1")
dam.Body = "Regards"
dam.Attachments.Add wPath & wFile
dam.Send

'deleting the saved workbook if wanted
If cblnDelFile Then Kill wPath & wFile

MsgBox "Email sent"

End Sub

Ciao,
Holger