Solved

Excel to PDF and Outlook

Posted on 2013-05-24
4
283 Views
Last Modified: 2013-05-28
Not sure how to go about this.... I have code to print excel to pdf and email.  In order to test I had to hard code the filename when it looked for the file to attach how do I have it capture the filename that is created when it is saved?  Horrible with loops/if statements I need to email to different depending on the value in B4 if B4 = ABC joe.cool@somewhere.com and john.doe@somewhere.com, B4 = DEF jane.doe@somewhere.com

Sub PrintEmail2()
    Dim fname As String
     
     '
     'create filename from workbookname, sheetname and todays date
    fname = ActiveSheet.Range("b4").Value & "-" & ActiveSheet.Name & "-" & ActiveSheet.Range("b6").Value & Format(Date, " YYYY.MM.DD")
     
     'change path to suit yourself.. currently points to "c:\temp\"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    "C:\users\jmac001\Desktop\" & fname, Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
    False
    
    Set Mail_object = CreateObject("Outlook.Application")
        With Mail_object.CreateItem(o)
        .Subject = "Forecast Updates"
        .To = ""
        .Body = ""
        .Attachments.Add "C:\users\jmac001\Desktop\TestFile 2013.05.24.pdf"
        .send
    
    End With

    
End Sub

Open in new window

0
Comment
Question by:jmac001
  • 2
  • 2
4 Comments
 
LVL 81

Expert Comment

by:byundt
ID: 39195647
Try it like this:
Sub PrintEmail2()
    Dim fname As String, Recipient As String
    Dim Mail_Object As Object, o As Object
     
     '
     'create filename from workbookname, sheetname and todays date
    fname = ActiveSheet.Range("b4").Value & "-" & ActiveSheet.Name & "-" & ActiveSheet.Range("b6").Value & Format(Date, " YYYY.MM.DD")
     
     'change path to suit yourself.. currently points to "c:\temp\"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\users\jmac001\Desktop\" & fname, Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    Select Case UCase(ActiveSheet.Range("B4").Value)
    Case "ABC"
        Recipient = "joe.cool@somewhere.com"
    Case "DEF"
        Recipient = "jane.doe@somewhere.com"
    Case Else
    End Select
    Set Mail_Object = CreateObject("Outlook.Application")
        With Mail_Object.CreateItem(o)
        .Subject = "Forecast Updates"
        .To = Recipient
        .Body = ""
        .Attachments.Add "C:\users\jmac001\Desktop\" & fname & ".pdf"
        .Send
    
    End With
    
End Sub

Open in new window

0
 

Author Comment

by:jmac001
ID: 39201231
Recieve a Outlook message box: A program is trying to send an email message on your behalf..... and has Allow/Deny/Help buttons, can this be coded to allow the email to be sent and the user not have to click allow?
0
 
LVL 81

Accepted Solution

by:
byundt earned 500 total points
ID: 39201611
I didn't get that message, but I did encounter a run-time error pointing to the With Mail_Object statement. The fix was to change an "o" to a "0"
Sub PrintEmail2()
    Dim fname As String, Recipient As String
    Dim Mail_Object As Object
     
     '
     'create filename from workbookname, sheetname and todays date
    fname = ActiveSheet.Range("b4").Value & "-" & ActiveSheet.Name & "-" & ActiveSheet.Range("b6").Value & Format(Date, " YYYY.MM.DD")
     
     'change path to suit yourself.. currently points to "c:\temp\"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\users\jmac001\Desktop\" & fname, Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    Select Case UCase(ActiveSheet.Range("B4").Value)
    Case "ABC"
        Recipient = "joe.cool@somewhere.com"
    Case "DEF"
        Recipient = "jane.doe@somewhere.com"
    Case Else
        Recipient = "barbara.flowers@xyz.net"
    End Select
    Set Mail_Object = CreateObject("Outlook.Application")
    With Mail_Object.CreateItem(0)
        .Subject = "Forecast Updates"
        .To = Recipient
        .Body = ""
        .Attachments.Add "C:\users\jmac001\Desktop\" & fname & ".pdf"
        .Send
    
    End With
    
End Sub

Open in new window

0
 

Author Closing Comment

by:jmac001
ID: 39202351
I did find some info on the message box it is a security alert, for now not going to disable will have the user allow.  Thanks for all your help.
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Are you unable to connect or configure Hotmail email account in Microsoft Outlook 2010, 2007? Or Outlook.com emails are not downloading to Outlook? Lets’ see the problem and resolve Outlook Connector error syncing folder hierarchy (0x8004102A).
This article lists the top 5 free OST to PST Converter Tools. These tools save a lot of time for users when they want to convert OST to PST after their exchange server is no longer available or some other critical issue with exchange server or impor…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question