Send active Worksheet as PDF via email with VBA

[Mmathieu]

So I have this code running to send E-Mail and backup into a Sharepoint Folder, so I got it to run, it saves as PDF into the folder, but i dont know why it sends as an Excel file.

The file is getting kinda big (10mb) and I would like to send only the activesheet as a PDF.

I'm no pro, but I found other posts in this forum that have similar codes to mine and seem to work but I cannot make it to work... plz help!

Private Sub CommandButton3_Click()
If MsgBox("Sauvegarde sous un nom different, envoie par courriel et FERME le fichier. Etes-Vous sur?", vbYesNo) = vbNo Then Exit Sub
'ActiveSheet.Unprotect "123"
Worksheets("Data").Range("SAVERNGFLAG") = 1
Worksheets("Expedition").Range("EXP") = ""
Range("Q1").Value = Date & " " & Time
Range("Q1").NumberFormat = "hhmm"
ThisFile = Range("S1").Value
ThisPath = Range("K2").Value
Dim xSht As Worksheet
Set xSht = ActiveSheet
Range("A21:G39").Copy
Sheets("DataPigs").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisPath & ThisFile & ""
'ActiveWorkbook.SaveAs Filename:=ThisPath & ThisFile & ".xlsm"
'ActiveWorkbook.Protect "123"
Dim objOutlook As Object
Dim objNameSpace As Object
Dim objInbox As Object
Dim objMailItem As Object
Set objOutlook = CreateObject("Outlook.Application")
Set objNameSpace = objOutlook.GetNamespace("MAPI")
Set objInbox = objNameSpace.Folders(1)
Set objMailItem = objOutlook.CreateItem(0)


Dim strTo As String
Dim I As Integer
strTo = ""
I = 2


With Worksheets("Data")
Do
strTo = strTo & .Cells(I, 4).Value & "; "
I = I + 1
Loop Until IsEmpty(.Cells(I, 1))
End With

strTo = Mid(strTo, 1, Len(strTo) - 2)
With objMailItem
.To = strTo
.CC = "; "
.Subject = "TEST! DÉSOLÉ!"
.Body = _
"Bonjour," & Chr(10) & Chr(10) & _
"Voici une copie du Rapport de quart" & Chr(10) & _
"Une copie du fichier est sauvegardé dans: https://riotinto.sharepoint.com/sites/RTFTACIRIE/Shared Documents/General/Archives/"
.Attachments.Add ActiveWorkbook.FullName
.Send
End With

Set objOutlook = Nothing
Set objNameSpace = Nothing
Set objInbox = Nothing
Set objMailItem = Nothing
Application.DisplayAlerts = False
Application.Quit

 

[6StringJazzer]

This line of code very specifically attaches the Excel file:

.Attachments.Add ActiveWorkbook.FullName

Try this:

.Attachments.Add ThisPath & ThisFile & ".pdf"

This assumes that ThisFile does not include the file extension.


By the way:

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisPath & ThisFile & ""

Why are you adding the null string to the end of a string? This has no effect.

 

[Mmathieu]

This line of code very specifically attaches the Excel file:

.Attachments.Add ActiveWorkbook.FullName

Try this:

.Attachments.Add ThisPath & ThisFile & ".pdf"

This assumes that ThisFile does not include the file extension.


By the way:

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisPath & ThisFile & ""

Why are you adding the null string to the end of a string? This has no effect.

Thank you sir! yes it used to have .xlsm as it would require it to save file as excel, i removed it without removing & "" so I could remember where to put it if i didnt save it as a PDF file! I will then remove it

 

[6StringJazzer]

If that worked, please mark as the solution. Good luck!

 

[Huuktkt]

How to create pdf file with password?

 

[6StringJazzer]

Excel can't do that natively. It might be possible if you have Adobe Acrobat Professional installed, which has a library that is callable from VBA.