Solved

Excel to PDF and Outlook

Posted on 2013-05-24
4
263 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 80

Expert Comment

by:byundt
Comment Utility
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
Comment Utility
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 80

Accepted Solution

by:
byundt earned 500 total points
Comment Utility
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
Comment Utility
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Learn more about how the humble email signature can be used as more than just an electronic business card. When used correctly, a signature can easily be tailored for different purposes by different departments within an organization.
Find out how to use dynamic social media in email signatures with this top 10 DOs & DON’Ts.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now