Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 332
  • Last Modified:

Excel to PDF and Outlook

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
jmac001
Asked:
jmac001
  • 2
  • 2
1 Solution
 
byundtCommented:
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
 
jmac001Author Commented:
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
 
byundtCommented:
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
 
jmac001Author Commented:
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now