Solved

Excel to PDF and Outlook

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

Sometimes Outlook might have problems sending a message. There may be various causes- corrupted PST, AV scanner etc. The message, instead of going to the Sent Items folder, sits in the Outbox indefinitely. To remove it you can use a free tool cal…
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…
To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

863 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

22 Experts available now in Live!

Get 1:1 Help Now