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

 

[MilkyTech]

Yes, some email clients use comma, others like Outlook use semi-colon:

.To = "johndoe@abc.com; janedoe@abc.com; joeydoe@abc.com" ' <-- Put email of the recipient here

 

[andyswin]

Thanks again - although I now have one last question (I hope!)

I am using this code in my spreadsheet 3 times, with 3 different modules, in order that I can email different data sets to different addresses etc.

In this part of the code:

  PdfFile = ActiveWorkbook.FullName
  i = InStrRev(PdfFile, ".")
  If i > 1 Then PdfFile = Left(PdfFile, i - 1)
  PdfFile = PdfFile & "[COLOR="#0000FF"]Company  Name[/COLOR]" & "[COLOR="#0000FF"]Delivery Report[/COLOR]" & ".pdf"

I am changing the parts highlighted in blue for each report; however, when I run each respective macro, they are all being produced with the same filename regardless.

Any ideas?

Sorry for all the questions, and thanks again for the help!

 

[MilkyTech]

I am using this code in my spreadsheet 3 times, with 3 different modules, in order that I can email different data sets to different addresses etc.
In this part of the code:

  PdfFile = ActiveWorkbook.FullName
  i = InStrRev(PdfFile, ".")
  If i > 1 Then PdfFile = Left(PdfFile, i - 1)
  PdfFile = PdfFile & "[COLOR=#0000FF]Company  Name[/COLOR]" & "[COLOR=#0000FF]Delivery Report[/COLOR]" & ".pdf"

I am changing the parts highlighted in blue for each report; however, when I run each respective macro, they are all being produced with the same filename regardless.

You can simplify the filename creation if you want. ZVA put that "if" statement in there for someone who had a specific issue. You probably don't need it.
This will suffice:

PdfFile = ActiveWorkbook.FullName
PdfFile = PdfFile & "[COLOR=#0000FF]Company  Name[/COLOR]" & "[COLOR=#0000FF]Delivery Report[/COLOR]" & ".pdf"

Now, a few questions:

1. About getting the same filename every time: Does each module (macro) you have assigned have a unique Sub name?
2. Are you changing the blue text within the code everytime? (are you opening the editor and changing the module's code each time you want to email?)
3. What exactly are you trying to achieve with your code as a whole? (explain in detail, there are many things that can be customized pretty easily such as the filenames (you can include a particular cell's content if you want, cell ranges, selected cells, you don't have to have the excel filename and extension included as the code above does, etc), how the email is filled out, signature, saving a copy of the pdf, where the pdf is saved, etc.)




-"Need more input!"

 

[andyswin]

Now, a few questions:

1. About getting the same filename every time: Does each module (macro) you have assigned have a unique Sub name?
2. Are you changing the blue text within the code everytime? (are you opening the editor and changing the module's code each time you want to email?)
3. What exactly are you trying to achieve with your code as a whole? (explain in detail, there are many things that can be customized pretty easily such as the filenames (you can include a particular cell's content if you want, cell ranges, selected cells, you don't have to have the excel filename and extension included as the code above does, etc), how the email is filled out, signature, saving a copy of the pdf, where the pdf is saved, etc.)

-"Need more input!"

Okay, so the 'cleaner' code is fine. That is, in fact, working better for me than the other, but in response to your questions -

1. Yes, each has a unique name.
2. Yes, changing the code each time.
3. I have a s/s set up to pull data through to 3 different 'reports' on 3 different tabs. The reports are all automated, I just wanted a way of emailing each one out as a PDF, without having to manually print to PDF each time, and then email - so this code works brilliantly for what I want it to do... but I am a complete beginner when it comes to VBA! I can read some of the code and sort of understand what it is doing, I just don't know what to change to make it work.

As I said, based on the 'cleaner' code you provided, this now appears to be working. Before, all the reports were sent with the same filename, regardless of what I wrote in blue.

When I press the macro buttons now, the report is generated and emailed with the filename: spreadsheet name.xlsmName In Blue Text.PDF. Ideally I would like it to just be sent with the name "Text In Blue.PDF" - but I can't figure that out either! :/

I really need to get some VBA lessons!

 

[MilkyTech]

Okay, so the 'cleaner' code is fine. That is, in fact, working better for me than the other, but in response to your questions -

1. Yes, each has a unique name.
2. Yes, changing the code each time.
3. I have a s/s set up to pull data through to 3 different 'reports' on 3 different tabs. The reports are all automated, I just wanted a way of emailing each one out as a PDF, without having to manually print to PDF each time

Ideally I would like it to just be sent with the name "Text In Blue.PDF" - but I can't figure that out either! :/

You should never need to change the code itself. That would defeat the whole purpose of the code which is to automate the process. Instead, have the code insert the value of a cell. Do you have a cell on your worksheet that is filled out with the "Company Name" that you want to name the pdf? If not just create one.
Any chance you can email me your workbook so I can figure out the best way to go about this for your particular situation? milkytech @ gmail

 

[andyswin]

Really all I want is to change

PdfFile = ActiveWorkbook.FullName

So that instead of using the workbook name, it simply uses whatever name I give it. I get that "Company name" & "delivery report" are added as extensions - through lack of a better term - I just want the file to start with something other than the Workbook name.

 

[MilkyTech]

Really all I want is to change

PdfFile = ActiveWorkbook.FullName

So that instead of using the workbook name, it simply uses whatever name I give it. I get that "Company name" & "delivery report" are added as extensions - through lack of a better term - I just want the file to start with something other than the Workbook name.

Well the issue is whether or not you are attaching the pdf to an email. This is why I wanted you to tell me in detail everything you want the code to do.
If you are attaching the pdf, the path to where the pdf is exported needs to be defined in order for the code to find it and attach it.
In this code:

PdfFile = ActiveWorkbook.FullName

the FullName property is defining that path and including the excel filename and extension.
For instance, if your excel doc is currently saved in your Documents folder, then "ActiveWorkbook.Fullname" will produce "C:\Users\MyUsername\Documents\MyWorkbook.xlsm". There are other ways to define the path and still name the pdf whatever you want, but I would need to know:
1. Do you want to attach the pdf to an email?
2. Do you want to save a copy of the pdf for yourself?
3. Will you be saving that copy in the same folder that your excel doc resides? If not, where do you want it saved?
4. Do you want anything else in the pdf filename other than the company name? Report #, Date, etc.?
5. Do you want the email filled out for you? who its being sent to, the body, etc.
6. Do you want your email signature added?

There is so much that can be done with VBA and its not difficult.
Don't worry about what the code does right now, tell me in detail everything you want the code to automate and we can go from there.

 

[andyswin]

1. Do you want to attach the pdf to an email?

Yes, but that was what this code was designed for, so it's already working.

2. Do you want to save a copy of the pdf for yourself?

Nope, but the code (so far as I can tell) automatically deletes the file without saving a copy.

3. Will you be saving that copy in the same folder that your excel doc resides? If not, where do you want it saved?

Not applicable.

4. Do you want anything else in the pdf filename other than the company name? Report #, Date, etc.?

Yes, but the code already allows me to do this here:

PdfFile = PdfFile & "Company Name" & "Delivery Report" & ".pdf"

5. Do you want the email filled out for you? who its being sent to, the body, etc.

Already done in the code below.

6. Do you want your email signature added?

Again, already in the code below.

I literally just want to change it so that the Workbook name is not part of the filename.

'My' Code -

Sub CompanyName() '<-- Remember to change the Sub name with each new counteparty
  Dim IsCreated As Boolean
  Dim i As Long
  Dim PdfFile As String, Title As String
  Dim OutlApp As Object
 
  ' Not sure for what the Title is
  Title = Range("A1")
 
  ' Define PDF filename

PdfFile = ActiveWorkbook.FullName
PdfFile = PdfFile & "Company Name" & "Delivery Report" & ".pdf" '<-- Change the name of the file to match the counterparty
 
  ' Export activesheet as PDF
  With Worksheets("TabA")
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
  End With
 
  ' Use already open Outlook if possible
  On Error Resume Next
  Set OutlApp = GetObject(, "Outlook.Application")
  If Err Then
    Set OutlApp = CreateObject("Outlook.Application")
    IsCreated = True
  End If
  OutlApp.Visible = True
  On Error GoTo 0
 
  ' Prepare e-mail with PDF attachment
  With OutlApp.CreateItem(0)
   
    ' Prepare e-mail
    .Subject = "Company - Biomass Deliveries - " & Range("E18").Value
    .To = "contact@XYZ.com" '<-- Put email of the recipient here
    .CC = "inbox@XYZ.com" '<-- Put email of 'CC' recipient here
    
    '<-- The 'Body' section contains the content of the email signature, only amend this if the address changes of group email changes
    
    .Body = "Hi," & vbLf & vbLf _
          & "Please find attached our delivery records for last week." & vbLf & vbLf _
          & "Please contact us at: inbox@XYZ.com if you have any issues." & vbLf & vbLf _
          & "Kind regards," & vbLf _
          & Application.UserName & vbLf & vbLf _
          & "Address & vbLf_


    .Attachments.Add PdfFile
   
    ' Try to send
    On Error Resume Next
    .Send
    Application.Visible = True
    If Err Then
      MsgBox "E-mail was not sent", vbExclamation
    Else
      MsgBox "E-mail successfully sent", vbInformation
    End If
    On Error GoTo 0
   
  End With
 
  ' Delete PDF file
  Kill PdfFile
 
  ' Quit Outlook if it was created by this code
  If IsCreated Then OutlApp.Quit
 
  ' Release the memory of object variable
  Set OutlApp = Nothing
 
End Sub

 

[MilkyTech]

4. Do you want anything else in the pdf filename other than the company name? Report #, Date, etc.?
Yes, but the code already allows me to do this here:

PdfFile = PdfFile & "Company Name" & "Delivery Report" & ".pdf"

Again, if your code and workbook are set up correctly, you will never need to open your VBA editor and change the code ever again. All changes should be made in your workbook, then the code can "import" those changes. You are already doing this in your ".Subject" line of code where you import the value of cell E18. I am assuming the "Company Name" is different for each report you email. You should have a cell in your workbook that contains this company name, where you can simply type a different company name here before you click your macro button.
ie:

PdfFile = PdfFile & ActiveSheet.Range("A1").Value & "Delivery Report" & ".pdf"

5. Do you want the email filled out for you? who its being sent to, the body, etc.
Already done in the code below.

Again, I assume you're not sending these reports to the same recipient everytime. If you are, great, just put that email address in the code and never change it, but if my assumption is correct and the recipients change, then you should again, import the value of a cell which is much easier to change than the code.
ie:

.To = ActiveSheet.Range("E10").Value

6. Do you want your email signature added?
Again, already in the code below.

Your code is not adding an email signature. It is simply adding text to the body of the email and then adding the current username. An email signature is one that you created in Outlook. The code can add that Outlook signature if you wanted.

I literally just want to change it so that the Workbook name is not part of the filename.

PdfFile = ThisWorkbook.Path & Application.PathSeparator & "Company Name" & "_Delivery Report" & ".pdf"

Again, you should import the Company Name from a cell. If the cell is on the same worksheet as your macro button, then it should look something like this:

PdfFile = ThisWorkbook.Path & Application.PathSeparator & ActiveSheet.Range("A1").Value & "_Delivery Report" & ".pdf"

If the Company Name is on a different sheet, then something like this:

PdfFile = ThisWorkbook.Path & Application.PathSeparator & ThisWorkbook.Sheets("TabA").Range("A19") & "_Delivery Report" & ".pdf"

Or even better, like this:

With ThisWorkbook
    PdfFile = .Path & Application.PathSeparator & .Sheets("TabA").Range("A19") & "_Delivery Report" & ".pdf"
End With

Also take note: Change ".Send" to ".Display" to view the email and make changes before sending. This is especially useful for testing your code. Once you are sure everything is working and your email looks exactly as you want it, then change it back to ".Send" to immediately send it without viewing.
Also, you can remove the line of code (or comment it out by adding an apostrophe in front of it) that makes a msgbox pop up to say the email was sent successfully. This is unnecessary and annoying to have to close that msgbox each time. The previous line of code that tells you if the email was not sent is all you need. If you don't get this error message, then you know it was successful.

All in all, I would have the code look something like this:

Sub CompanyName()
  Dim IsCreated As Boolean
  Dim PdfFile As String
  Dim OutlApp As Object


  ' Define PDF filename
  With ThisWorkbook
    PdfFile = .Path & Application.PathSeparator & .Sheets("TabA").Range("A19") & "_Delivery Report" & ".pdf"
  End With
 
  ' Export activesheet as PDF
  With Worksheets("TabA")
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
  End With
 
  ' Use already open Outlook if possible
  On Error Resume Next
  Set OutlApp = GetObject(, "Outlook.Application")
  If Err Then
    Set OutlApp = CreateObject("Outlook.Application")
    IsCreated = True
  End If
  OutlApp.Visible = True
  On Error GoTo 0
 
  ' Prepare e-mail with PDF attachment
  With OutlApp.CreateItem(0)
   
    ' Prepare e-mail
    .Subject = "Company - Biomass Deliveries - " & Range("E18").Value
    .To = ActiveSheet.Range("E10").Value '<-- Put email of the recipient here
    .CC = ActiveSheet.Range("E11").Value '<-- Put email of 'CC' recipient here
    
    '<-- The 'Body' section contains the content of the email signature, only amend this if the address changes of group email changes
    
    .Body = "Hi," & vbLf & vbLf _
          & "Please find attached our delivery records for last week." & vbLf & vbLf _
          & "Please contact us at: inbox@XYZ.com if you have any issues." & vbLf & vbLf _
          & "Kind regards," & vbLf _
          & Application.UserName & vbLf & vbLf _
          & Address & vbLf_                                   ' <---This line isn't doing anything




    .Attachments.Add PdfFile
   
    ' Try to send
    On Error Resume Next
    .Display
    Application.Visible = True
    If Err Then
      MsgBox "E-mail was not sent", vbExclamation
    Else
    ' MsgBox "E-mail successfully sent", vbInformation
    End If
    On Error GoTo 0
   
  End With
 
  ' Delete PDF file
  Kill PdfFile
 
  ' Quit Outlook if it was created by this code
  If IsCreated Then OutlApp.Quit
 
  ' Release the memory of object variable
  Set OutlApp = Nothing
 
End Sub

 

[PMcDonald]

Hello:

I am using your code to convert an excel worksheet to a pdf and email it as an attachment. I have Excel and Outlook 2013. I allow the user to enter the email addresses he wants to send this to, and allow him to send the email. The code I am using (with your help) works beautifully, as follows:

Sub AttachActiveSheetPDF_01()
  Dim IsCreated As Boolean
  Dim PdfFile As String, Title As String
  Dim OutlApp As Object

   ' Define PDF filename
  Title = Range("C218").Value
  PdfFile = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\" & Title & ".pdf"


  ' Exportactivesheet as PDF
  With ActiveSheet
    .ExportAsFixedFormat Type:=xlTypePDF, FileName:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
  End With

  ' Use already open Outlook if possible
  On Error Resume Next
  Set OutlApp = GetObject(, "Outlook.Application")
  If Err Then
    Set OutlApp = CreateObject("Outlook.Application")
    IsCreated = True
  End If
  OutlApp.Visible = True
  On Error GoTo 0

  ' Prepare e-mail with PDF attachment
  With OutlApp.CreateItem(0)
   
    ' Prepare e-mail
    .Subject = Title
    .To = "" ' <-- Put email of the recipient here
    .CC = "" ' <-- Put email of 'copy to' recipient here
    .Body = "Hello," & vbLf & vbLf _
          & "Please find attached a completed case review." & vbLf & vbLf _
          & "Thank you," & vbLf _
          & Application.UserName & vbLf & vbLf
    .Attachments.Add PdfFile
   
    ' Try to send
    Application.Visible = True
    .Display
  End With

  ' Quit Outlook if it was not already open
  If IsCreated Then OutlApp.Quit

  ' Release the memory of object variable
  Set OutlApp = Nothing

End Sub

After the user sends the email, is it possible to save a pdf of the email to a network drive, Path = "Z:\Emails - EAST\2016\"FileName = Range("C218")? This would provide proof that the email with attachment was definitely sent to the email recipient.

Thanks very much for any help!