email as PDF

[manmah]

Just found out I can send a worksheet as PDF via email by using the home button. however does anyone know if there is a way of automatically putting the email address on the worksheet into the email to save copying and pasting from the sheet???

Thanks
Mark

 

[Andrew Poulsom]

Look here:

http://www.rondebruin.nl/pdf.htm

 

[Michael M]

Hi Mark
Here's some code to create a PDF and E-Mail it.
I also have code to send to a number of different recipients

Sub DoALLsingle()
'Assistance gratefully received from Dom Hill, JBeaucaire, James Vaughan and Snowblizz, APRIL 2010
'For this code to work the Adobe Distiller VBA library must be enabled
'Select Tools...References in the VBA editor and check it from the list
'Printer Preferences for "Adobe PDF" printer need to have "Rely on system Fonts" unchecked

Dim tempPDFFileName, tempPSFileName, tempPDFRawFileName As String, mypdfDist As New PdfDistiller, _
 i As Integer, Mail_Object, Email_Subject, o As Variant
    tempPDFRawFileName = "G:\Temp\" & ActiveWorkbook.Name ' Change File Path to suit
    tempPSFileName = tempPDFRawFileName & ".ps"
    tempPDFFileName = tempPDFRawFileName & ".pdf"
    ActiveSheet.PrintOut Copies:=1, preview:=False, ActivePrinter:="Adobe PDF", _
        printtofile:=True, Collate:=True, prtofilename:=tempPSFileName
    mypdfDist.FileToPDF tempPSFileName, tempPDFFileName, ""
     Kill tempPSFileName
Set mypdfDist = Nothing

'************End of PDF section*************
'************Start of emailing code*********
    Set Mail_Object = CreateObject("Outlook.Application")
        With Mail_Object.CreateItem(o)
            .Subject = "SUBJECT LINE" ' CHANGE TO SUIT
            .To = "RECIPIENTS ADDRESS" 'CHANGE TO SUIT
            .Body = "E MAIL TEXT GOES HERE" & Chr(13) & Chr(13) & "Regards," & Chr(13) & "YOUR NAME." & Chr(13) & "YOUR ADDRESS." 'Change comments to suit
            .Attachments.Add tempPDFFileName
            .Send
    End With
        MsgBox "E-mail successfully sent", 64
        Application.DisplayAlerts = False
Set Mail_Object = Nothing
End Sub

 

[manmah]

Thanks guys. However, we use groupwise (unfortunately) Any Ideas???

Also Micheal M i tried to run the code but it came up with the following error: user defined type not defined???

Thanks
Mark

 

[Michael M]

Mark
Did you follow the instructions at the beginning of the code ??

 

[manmah]

I have now sorry me being too eager!!!! but i can't find Adobe distiller in the references. I'm probably being really dense here??

Thanks
Mark

 

[Andrew Poulsom]

Maybe you don't have Distiller. It comes with Adobe Acrobat, which you have to buy.

 

[manmah]

Thanks to both of you. we don't have acrobat. we are hoping to get outlook in the near future so will try again...

Thanks
Brgds
Mark