none
MACRO TO SAVE AS PDF WITH NAMES AS PER CELL VALUES AND PRINT AND SEND BY EMAIL (OUTLOOK) RRS feed

  • Question

  • MACRO TO SAVE SHEET AS PDF FILE IN A GIVEN FOLDER BY AUTOMATICALLY GIVING NAME FROM CELL VAUES(B1,C1,D1). CELL C1 CONTAINS  DATE  (ANY CHOOSEN DATE NOT TODAY OF FORMAT DD MM YYYY).

    AUTOMATICALLY PRINT AND

    SEND BY E MAIL

    Tuesday, September 5, 2017 1:45 PM

All replies

  • Hi SUBINIGEORGE,

    You could use ExportAsFixedFormat to save the Worksheet as PDF. Use Sheets.PrintOut Method to print the worksheet.

    I'm wondering how do you want to send by email? Do you want to send the single worksheet as attachments via Outlook?

    If you so you need save the worksheet to a new workbook and then send the new workbook via email.

    Here is the example.

    Sub Test()
    
    'need add reference to Microsoft Outlook object library
    
    Application.ScreenUpdating = False
    
    'combine pdf name
    
    Dim ws As Worksheet
    
    Set ws = ActiveSheet
    
    B1 = ws.Range("B1")
    
    C1 = ws.Range("C1")
    
    C1 = Format(C1, "YYYYMMDD")
    
    D1 = ws.Range("D1")
    
    Filename = B1 & C1 & D1
    
    'save folder path
    
    FOLDERPATH = ActiveWorkbook.Path
    
    'save as pdf
    
    ws.ExportAsFixedFormat xlTypePDF, FOLDERPATH & "\" & Filename
    
    'print worksheet
    
    ws.PrintOut
    
    'save the single worksheet to a new workbook
    
    Dim tempWb As Workbook
    
    Dim tempWs As Worksheet
    
    tempfile = FOLDERPATH & "\" & "Temp.xlsx"
    
    If Dir(tempfile) <> "" Then
    
    Kill tempfile
    
    End If
    
    Set tempWb = Application.Workbooks.Add
    
    Set tempWs = tempWb.Worksheets(1)
    
    ws.Cells.Copy tempWs.Cells
    
    tempWs.Name = ws.Name
    
    tempWb.SaveAs tempfile
    
    tempWb.Close
    
    'create outlook mail item
    
    Dim olApp As Outlook.Application
    
    Dim olMail As Outlook.MailItem
    
    Set olApp = CreateObject("Outlook.Application")
    
    Set olMail = olApp.CreateItem(olMailItem)
    
    olMail.To = "test@mailaddress.com"
    
    olMail.Subject = "A test mail"
    
    olMail.Attachments.Add (tempfile)
    
    olMail.Display
    
    Set olMail = Nothing
    
    Set olApp = Nothing
    
    Kill tempfile
    
    Application.ScreenUpdating = True
    
    End Sub

    If I misunderstand anything, please feel free to let me know.

    Best Regards,

    Terry

    Wednesday, September 6, 2017 5:43 AM