Results 1 to 9 of 9

Thread: [RESOLVED] Email PDF from VBA Code?

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Feb 2004
    Posts
    155

    Resolved [RESOLVED] Email PDF from VBA Code?

    I have written code to create PDF's in excel. I was wondering if it is possible, that after I create the PDF, to email that PDF file to a specified recipient. Any help or suggestions would be greatly appreciated.....even if the answer is "It's not possible". This way I don't keep racking my brain trying to find a solution. Thanks!

  2. #2
    Frenzied Member
    Join Date
    Feb 2003
    Location
    Argentina
    Posts
    1,950

    Re: Email PDF from VBA Code?

    You should be able to program Outlook from Excel to send it as an attachment, assuming you have Outlook. Don't know how you'd do it with other email programs.
    Tengo mas preguntas que contestas

  3. #3

    Thread Starter
    Addicted Member
    Join Date
    Feb 2004
    Posts
    155

    Re: Email PDF from VBA Code?

    Yes, I have Outlook but I don't know how to code this with an attachement. I can easily send excel workbooks but not other file extension attachements.

  4. #4
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710

    Re: Email PDF from VBA Code?

    Sure its possible. You can use the Outlook Object Model inside of Excel but you will get the Security Prompt, which is no big deal since your not doing a batch mailing or stuff.

    Search by my username and outlook and attachment and you should find many examples to go over.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  5. #5

    Thread Starter
    Addicted Member
    Join Date
    Feb 2004
    Posts
    155

    Re: Email PDF from VBA Code?

    Actualy I will be doing batch emailing. There may be 100 different PDF's created (one for each supplier). I then would like to send just that PDF to the corrisponding supplier. Apparently the security prompt will be an issue. Is there anyway around this? Also, I have tried a search with your suggestion but I'm not having luck finding the simple code to send an email with an attachement.

  6. #6
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710

    Re: Email PDF from VBA Code?

    Here is a result from my search. Some code of mine from a few years ago.

    http://www.vbforums.com/showpost.php...52&postcount=4
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  7. #7
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710

    Re: Email PDF from VBA Code?

    There is no way in VB to bypass the prompt without re-writting your app as an Outlook Add-In or by using a third party product. Third party products all require the system to be logged on and not locked.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  8. #8
    Frenzied Member
    Join Date
    Feb 2003
    Location
    Argentina
    Posts
    1,950

    Re: Email PDF from VBA Code?

    Here's an example from an app from last year in VB.Net. The principle of programming outlook should be similar.
    VB Code:
    1. Public Sub LetsSendMail()
    2.         Dim OlApp As New Outlook.Application()
    3.         Dim OlNameSpace As Outlook.NameSpace
    4.         Dim msg As Outlook.MailItem
    5.         Dim strPath, strSubj, strName As String
    6.  
    7.         strPath = strRelPath & gstrPepType & "\" & Current.Proj & "\" & Current.Proj & "Changes.txt"
    8.         strSubj = Current.Proj & " " & Current.Mnth.ToUpper & ", " & Current.YNum & " Edited Changes"
    9.         strName = "Your client's email"
    10.  
    11.         Try
    12.             msg = OlApp.CreateItem(Outlook.OlItemType.olMailItem)
    13.             msg.Recipients.Add(strName)
    14.             msg.Subject = strSubj
    15.             msg.Attachments.Add(strPath)
    16.             msg.Body = "Changes attached for " & Current.Mnth.ToUpper & Current.Proj.ToUpper
    17.             msg.Display()
    18.         Catch ex As Exception
    19.             MessageBox.Show(ex.Message)
    20.         End Try
    21.     End Sub
    This doesn't send the email, because I review it first. You may want to get client's emails & which the path to their pdf from a table or file. Put it in a loop to send multiple emails. I don't know any way to avoid sending 50 separate emails if all 50 clients get a separate attachment, though.
    Tengo mas preguntas que contestas

  9. #9

    Thread Starter
    Addicted Member
    Join Date
    Feb 2004
    Posts
    155

    Re: Email PDF from VBA Code?

    I got it working. Thanks for the help!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width