Save Worksheet as PDF and Attach in Outlook

[leewoo5]

Hi all,

I would like to use VBA to save a worksheet as a PDF in this location:

I:\FST\R&D and Projects\Samples\Sample Requests\

and then attach the PDF copy to outlook with the recipient and cc addresses already attached.

The issue I am facing is that the sheet I would like to save as a PDF is different to the one that that macro button will be placed on. I would like for the sheet named "Request" to save as the PDF and the button to be on the sheet that I have called "Log".

I would also love for the save name to be cell B4 on the Log page.

I appreciate any support that can be given with this.

I can also post the file if it would help!

[Kenneth Hobs]

Welcome to the forum!

Change the email fields to suit as I did not know what you needed there. Add the Outlook reference in the Visual Basic Editor (VBE) Tools > References menu as commented.

Place code into a Module and call from either of the two command button control types or use directly in the ActiveX control. There are many tutorials that explain those two controls. e.g. https://support.office.com/en-us/art...ad=US&fromAR=1

'More Excel to Outlook Examples: http://www.rondebruin.nl/win/s1/outlook/bmail4.htm
'http://www.rondebruin.nl/win/s1/outlook/signature.htm


'Add reference: Microsoft Outlook xx.x Library, where xx.x is 14.0, 15.0, 16.0, etc.
Sub Main()
  Dim olApp As Outlook.Application, olMail As Outlook.MailItem
  Dim pdfPath$
  
  pdfPath = "I:\FST\R&D and Projects\Samples\Sample Requests\"
  pdfPath = pdfPath & Worksheets("Log").Range("B4").Value2
  Worksheets("Request").ExportAsFixedFormat xlTypePDF, pdfPath
  
  Set olApp = New Outlook.Application
  
  Set olMail = olApp.CreateItem(olMailItem)
  With olMail
    .To = "to@to.com"
    .CC = "cc@cc.com"
    .Subject = "Subject"
    .Body = "Body"
    .Attachments.Add pdfPath
    .Display
    '.Send
  End With
  
  Set olMail = Nothing
  Set olApp = Nothing
End Sub

[leewoo5]

Thank you so much for the quick reply.

I am having a "Compile error: User-defined type not defined" with the following in bold and underlined:

'More Excel to Outlook Examples:



'Add reference: Microsoft Outlook xx.x Library, where xx.x is 14.0, 15.0, 16.0, etc.

Sub savepdftest()
Dim olApp As Outlook.Application, olMail As Outlook.MailItem
Dim pdfPath$
     pdfPath = "I:\FST\R&D and Projects\Samples\Sample Requests\"
pdfPath = pdfPath & Worksheets("Log").Range("B4").Value2
Worksheets("Request").ExportAsFixedFormat xlTypePDF, pdfPath
     Set olApp = New Outlook.Application
     Set olMail = olApp.CreateItem(olMailItem)
With olMail
.To = "***"
.CC = "***"
.Subject = "Sample Request"
.Body = "Body"
.Attachments.Add pdfPath
.Display
'.Send
End With
     Set olMail = Nothing
Set olApp = Nothing
End Sub

Please forgive my ignorance, I am completely inexperienced.

Many thanks,

[Kenneth Hobs]

When pasting code, please do so between code tags. Click the # icon on the toolbar to add code tags easily.

Add the Outlook reference in the Visual Basic Editor (VBE) Tools > References menu as commented.

https://msdn.microsoft.com/en-us/lib.../gg264402.aspx

References added like that and Dim'ed accordingly, allows intellisense to work for that object if coded to do that. This is called Early Binding. When you just Dim an object as the generic Object, that is called Late Binding. There are pros and cons for both ways. Though a bit more work for some, in the end, I prefer Early Binding for my objects. Otherwise, VBA becomes more a scripting language like VBS.

[Bob Phillips]

I prefer late binding myself

Sub Main()
    Dim olApp As Object, olMail As Object
    Dim pdfPath$
     
    pdfPath = "I:\FST\R&D and Projects\Samples\Sample Requests\"
    pdfPath = pdfPath & Worksheets("Log").Range("B4").Value2
    Worksheets("Request").ExportAsFixedFormat xlTypePDF, pdfPath
     
    Set olApp = CreateObject(, "Outlook.Application")
     
    Set olMail = olApp.CreateItem(olMailItem)
    With olMail
        .To = "to@to.com"
        .CC = "cc@cc.com"
        .Subject = "Subject"
        .Body = "Body"
        .Attachments.Add pdfPath
        .Display
         '.Send
    End With
     
    Set olMail = Nothing
    Set olApp = Nothing
End Sub

[leewoo5]

Much appreciated.

I think the last thing I am having issues with is the following:

.Attachments.Add pdfPath

This is displaying error "Cannot find this file. Verify path and file name are correct."

Many thanks,

[Kenneth Hobs]

While not always needed, in this case, and I like to do it anyway, let's be more explicit and concatenate the file extension.

  pdfPath = pdfPath & Worksheets("Log").Range("A4").Value2 & ".pdf"

Take xld's advice over mine.

Of late, I have decided that though more work by explaining twice as you needed, I like to teach new coders to use the object reference so that they can learn more on their own. Sometimes I do both methods and comment out one but then that confusers some new coders.

Here is one advantage to early binding: Press F1 in or next to a command word. In this case, let's say Attachments. If early bound, help will show that object or method or property directly. What can I say, I forget what all I can do with objects myself sometimes...

[leewoo5]

Thank you guys so much. This is perfect for my application!

One last thing, is it easy to create the file name from 2 different cells?

Currently the file name saves as cell B4, which in my form is the request number. If I would like to add cell D4 after that, which is the customer name, is it possible?

So the save file would read: "004 - Customer Name.pdf"

Many thanks,

[Kenneth Hobs]

Yes.

It is just a matter of string concatenation.

With Worksheets("Log")  
  pdfPath = pdfPath & .Range("B4").Value2 & " - " & .Range("D4").Value2 & ".pdf"
End With

[Bob Phillips]

I'll step back fro the solution as Kenneth is more than capable of helping you get there, but just to add to the early/late binding discussion. As ever, Kenneth is making a good point about understanding the object's methods an d properties, but I have been burnt so many times with early-binding code out in the field. As such, if I am developing something new, I use early-binding to develop, and then release it as late bound. I even wrote a blog on the topic Develop Early, Release Late