How to email and print userform, worksheet and pdfs from file in one print job?

Simon Lloyd

Administrator
Joined
Apr 2, 2011
Messages
401
Reaction score
0
Points
0
Location
Manchester, England
Excel Version(s)
Excel 2016
Hi all,
I am having a little trouble getting a few different printable items to print in one print and email job. I can print the userform with Me.PrintForm but have no idea how to email a copy, I know how to print worksheets and email them and I know how to select a file from a folder to email and print - but how would I get them all in one package to email and print?

Kind regards,
Simon
 
The way I did it in the past was to use a scratch worksheet and use API's rather than me.printform to copy/paste the userform.

e.g.
http://www.vbaexpress.com/forum/showthread.php?24016-Setting-Application-ActivePrinter-and-PrintForm
https://www.excelforum.com/excel-pr...rm-to-pdf-and-then-attach-it-to-an-email.html
https://www.excelforum.com/excel-pr...y-userform-to-clipboard-and-save-as-jpeg.html

Code:
Declare Sub keybd_event Lib "user32" (ByVal bVk As Byte, _
                                      ByVal bScan As Byte, ByVal dwFlags As Long, ByVal dwExtraInfo As Long)


Public Const VK_SNAPSHOT = 44
Public Const VK_LMENU = 164
Public Const KEYEVENTF_KEYUP = 2
Public Const KEYEVENTF_EXTENDEDKEY = 1


Sub CallUserForm()
    frmempform.Show
End Sub


Public Sub SavePDF()
    Dim myPath As String
    Dim myName As String
    Dim myfile As String


    myPath = ThisWorkbook.Path & "\"
    DoEvents
    keybd_event VK_LMENU, 0, KEYEVENTF_EXTENDEDKEY, 0
    keybd_event VK_SNAPSHOT, 0, KEYEVENTF_EXTENDEDKEY, 0
    keybd_event VK_SNAPSHOT, 0, KEYEVENTF_EXTENDEDKEY + KEYEVENTF_KEYUP, 0
    keybd_event VK_LMENU, 0, KEYEVENTF_EXTENDEDKEY + KEYEVENTF_KEYUP, 0
    DoEvents
    Workbooks.Add
    Application.Wait Now + TimeValue("00:00:01")


    With ActiveSheet
        myName = frmempform.txtempid.Value
        .Name = myName
        .PasteSpecial Format:="Bitmap", Link:=False, DisplayAsIcon:=False
        .Range("A1").Select
        .PageSetup.Orientation = xlLandscape
        .PageSetup.FitToPagesWide = 1
        .PageSetup.Zoom = False
        .ExportAsFixedFormat Type:=xlTypePDF, filename:=myPath & myName & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, _
                             IgnorePrintAreas:=False, OpenAfterPublish:=False
    End With
    ActiveWorkbook.Close False


    myfile = myPath & myName & ".pdf"


    Call Send_Mail(myfile)
    Kill myfile
End Sub


Sub Send_Mail(myfile As String)
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strTo As String
    Dim strSubject As String
    Dim strBody As String


    strTo = frmempform.txtempid.Value
    strSubject = "New Customer Info"
    strBody = "Please see attached"


    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)


    With OutMail
        .To = strTo
        .CC = ""
        .BCC = ""
        .Subject = strSubject
        .Body = strBody
        .Attachments.Add myfile


        .Display   'or use .Send
    End With
    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub


Or, I can attach an example file or two.
 
Back
Top