Macro to print first 2 pages of current sheet as pdf and email

[Tosborn]

Macro to print first 2 pages of current sheet as pdf and email

Hey there,

Could someone please help me out with a macro. Need the macro to print out the first 2 pages of the active sheet as a pdf and save in a specified directory.

Could we then have another macro to complete the same as above but then also prepare an email with the pdf attached to the email. The email address to send to is stored in B12 of the active sheet.

We could also have the cc of the email and the body of the email also stored in U7 & U8 of the active sheet.

Thanks so much. This is gonna save me a bunch of time.

Love my mr. excel msg board, complete life saver.

Tim

 

[Worf]

Like this:

Sub CreateOutlook()
Dim wks As Worksheet, OutApp As Outlook.Application, OutMail As Outlook.MailItem, filestr$
Set wks = ActiveSheet
filestr = wks.[b15] & wks.[b16]            ' cell B15=c:\pub\              cell B16=bt.pdf
wks.ExportAsFixedFormat xlTypePDF, filestr, , , False, 1, 2, False
Set OutApp = CreateObject("Outlook.application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
    .To = wks.[b12]
    .cc = wks.[b13]
    .BCC = "testing@live.com"
    .Subject = "Meet up to Discuss"
    .Body = wks.[b14]
    .Attachments.Add filestr
    .Display
End With
Set OutMail = Nothing
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub

 

[Tosborn]

Champion, that works a treat.

Cheers,
Tim

 

[Worf]

You are welcome and thanks for the feedback.