VBA code to convert excel to pdf and email it as attachment

[aarondesin91]

Dear Forumers,

I really need your help. I am new to this whole VBA coding thing have no basic at all in programming and stuff so please help me out here. I am currently assigned a project where I have to create a excel sheet which act as a templete for sending request. The requirement of the project is that I need a vba code for a button when i click it, it will convert my active sheet alone to pdf, automatically save it with the title captured from a cell in the active sheet which is entered by the user. Email this pdf as a attachment to the specific person. Please help me out, my job depends on this project please guys out there.

Thank you

 

[ZVI]

Does an auto signature set on your Outlook?
If not then just comment that line: 'Signature = .Body
and edit the Message = "Text of the email body" accordingly.

 

[Jeson Joshua]

Yes, Auto signature is set on my outlook.

Also, Now this is the debug coming up:

Sh.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

 

[ZVI]

...MS office 2013 version.

Note, just for the case: code will not work on Home or Student editions because CreateObject() method is not supported by those versions.

 

[ZVI]

Yes, Auto signature is set on my outlook.

Please try commenting 'Signature = .Body code line for debugging purpose.

 

[Jeson Joshua]

It is working. Wohoooo.... Thanks a million man.. You are the real MVP.. Appreciate it.

 

[ZVI]

It is working. Wohoooo.... Thanks a million man.. You are the real MVP.. Appreciate it.

I'm happy we have sorted this out!

 

[Marhier]

Good day to you all.
Sorry to intrude on this thread, but I am having a similar issue I was hoping someone might be able to help me with; I thought best not to post a new thread?

The code I'm using takes the current worksheet, exports it as a .pdf, brings up the 'Save As' box and once saved, a message pops up saying:
"Copy saved. An email will now be created.".

The issue I'm having is, I can't for the life of me get it to open up an email with the .pdf attached.

Please note that I'd like it to send via the user's default mail client, as the users accessing this file are either on Outlook or Lotus Notes etc.

Sub ExporttoPDF()Dim wsA As Worksheet
Dim wbA As Workbook
Dim strTime As String
Dim strName As String
Dim strPath As String
Dim strFile As String
Dim strPathFile As String
Dim myFile As Variant
On Error GoTo errHandler


Set wbA = ActiveWorkbook
Set wsA = ActiveSheet


myFile = Application.GetSaveAsFilename _
    (InitialFileName:=strPathFile, _
        FileFilter:="PDF Files (*.pdf), *.pdf", _
        Title:="Select Folder and FileName to save")
        
    MsgBox "Copy saved. An email will now be created." _
      & vbCrLf _
      & myFile


If myFile <> "False" Then
    wsA.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        FileName:=myFile, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=True


End If


exitHandler:
    Exit Sub
errHandler:
    MsgBox "Could not create PDF file"
    Resume exitHandler
End Sub[CODE]


Any help would be greatly appreciated.
Thank you.
:)

 

[safy89]

Hi All!

Great Code, Big Thanks!
Can we make that the message don't change the text form?

I use this:

With OutlApp.CreateItem(0)
.Attachments.Add PdfFile


With .GetInspector: End With
Signature = .HTMLBody
' Prepare e-mail

.Subject = Title
.To = Range("C8") ' <-- Put email of the recipient here
.CC = "teleki.zsuzsanna@aco.hu; nemeth.gabor@aco.hu" ' <-- Put email of 'copy to' recipient here
Message = "Tisztelt " & Range("C7") & "!" & vbLf & vbLf _
& "Csatoltan küldöm a telefonon is említett, szerződés szerinti teljesítés igazolásunkat!" & vbLf & vbLf _
& "Legyenek szívesek cégszerűen aláírva, digitálisan is megküldeni részemre, és Teleki Zsuzsanna kolléganőm részére!" & vbLf & vbLf _
& "Köszönöm!"
.HTMLBody = Replace(Message, vbLf, Chr(60) & "br" & Chr(62)) & Signature

It works great, make everything I want, it has one problem, it change the message text style. How can I make that it use the original text style?

Thanks for answers!

Safy89

 

[Padthelad]

ooopppppppssssssssssss

 

[Jeson Joshua]

Hi MVP ,

I came across the same case but this time I dont need the sheets to get converted to PDF but I need each sheet itself with other details remaining the same. I tried to change few things in the coding with it isnt working.

Could you please help?

Thank you.