Create pdf from spreadsheet and email All using code

[daniels012]

We use Primopdf to convert a spreadsheet to pdf.
I was wondering if there is a way to write code that will:
1) Current print area go to print to Primopdf
2) enter the name for the file from Cell C6
3) Send this file to Outlook
4) Enter the email address from cell M5

Thank You,
Michael

 

[Davers]

Hi Michael, something like this should work:

Sub CreateEmail()

Dim myTo As String
Dim myCC As String
Dim myDate As String
Dim mySub As String
Dim appOutlook As Object
Dim MailItem As Object
Dim myAttach As String

Set appOutlook = CreateObject("Outlook.Application")
Set MailItem = appOutlook.CreateItem(olMailItem)

myTo = "me@here.com"
myCC = "you@there.com"
myDate = Date
mySub = "Here is the test file I'm sending on " & myDate

myAttach = "C:\MyTestFile.xls" ' Change the directory to your own along with the file name.

With MailItem
    .To = myTo
    .CC = myCC
    .Subject = mySub
    .Body = "This is a simple body."
    .Attachments.Add myAttach
    .Display
End With
    
    Set appOutlook = Nothing
    Set MailItem = Nothing
    
End Sub

You can make the email as simple as you'd like, as in the above example, or as complicated...such as using HTML...

Hope this helps!

Dave

 

[daniels012]

Just curious,
Will this work with the pdf file I just created??

Michael

 

[Davers]

Hi Michael, it should, you would just need to change the directory and file name....

Dave

 

[daniels012]

Some how I have to save the new name of the file I created, then add the extension pdf to it???

Michael

 

[Davers]

Yes, you could use your spreadsheet to tell it where your file is and what the name of it is the same way you use it to tell your code where to save it and with what name. An example is below:

Sub SaveAs()

Dim myFile As String
Dim myDir As String

myDir = Worksheets("Main").Range("D7").Value
myFile = Worksheets("Main").Range("D8").Value

ThisWorkbook.SaveAs myDir & myFile
    
End Sub

Sub CreateEmail()

Dim myTo As String
Dim myCC As String
Dim myDate As String
Dim mySub As String
Dim appOutlook As Object
Dim MailItem As Object
Dim myAttach As String

Set appOutlook = CreateObject("Outlook.Application")
Set MailItem = appOutlook.CreateItem(olMailItem)

myTo = "me@here.com"
myCC = "you@there.com"
myDate = Date
mySub = "Here is the test file I'm sending on " & myDate
myDir = Worksheets("Main").Range("D7").Value
myFile = Worksheets("Main").Range("D8").Value

myAttach = myDir & myFile ' Change the directory to your own along with the file name.

With MailItem
    .To = myTo
    .CC = myCC
    .Subject = mySub
    .Body = "This is a simple body."
    .Attachments.Add myAttach
    .Display
End With
    
    Set appOutlook = Nothing
    Set MailItem = Nothing
    
End Sub

I've also created a working version of this in an Excel Workbook. If you'd like me to email it to you, just email me at:

dmorri18@"removethispart"ford.com

Dave

 

[daniels012]

Here is the part I have now that creates a pdf.

ActiveWorkbook.Save 
    Path = "C:\Emailed Proposals\" 
        On Error Resume Next 
        ActiveWorkbook.SaveAs Filename:= _ 
            Path & "Proposal" & _ 
        Str(Application.Range("O3").Value), FileFormat:=xlNormal _ 
            , Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _ 
            CreateBackup:=False 
        On Error GoTo 0 
    Application.ActivePrinter = "PrimoPDF on Ne00:" 
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _ 
        "PrimoPDF on Ne00:", Collate:=True 
    Application.ActivePrinter = "EPSON Stylus C86 Series on Ne02:"

I need to somehow take this newly named file and enter it in an email. I understand yours that you gave me. But it's for an excel file (Isn't it?) If I could convert the file to pdf. then send it in an email, this would be great.

Thank You,
Michael

 

[Norie]

Michael

Davers code would attach any file to an email, not just an Excel file.

Just specify the correct path and filename here.

myAttach = myDir & myFile ' Change the directory to your own along with the file name.

 

[daniels012]

OK,
I won't know what that name will be though will I?
Can I do (as in his example)
myFile = Worksheets("Main").Range("D8").Value &".pdf" or will this put an .xls extention automatically?

I guess I need to know how to combine the 2 pieces of code???

Michael

 

[Norie]

Michael

Why would that put an Excel extension on the filename?

 

[Davers]

Hey again! You would just need to replace this line in the email code:

myAttach = myDir & myFile ' Change the directory to your own along with the file name.

with this line that you created:

myFile = Worksheets("Main").Range("D8").Value &".pdf"

and then change the attachment line of code:

.Attachments.Add myAttach

to use your name of myFile:

.Attachments.Add myFile

This way, it will attach the pdf file you created and look for it where you saved it...

Keep posting if you need more help!!

And hello Norie! It's been a while since I've visited the board!!! You've helped me out a lot in the past!

Dave