Results 1 to 10 of 10

Thread: VBA to save as PDF and mail PDF to Specific recipient/s from Specific Account with Specific Signature

  1. #1
    Board Regular WERNER SLABBERT's Avatar
    Join Date
    Mar 2009
    Posts
    98
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Angry VBA to save as PDF and mail PDF to Specific recipient/s from Specific Account with Specific Signature

    Hi there ....
    i recently found this thread
    "
    HTML Code:
    https://www.mrexcel.com/forum/general-excel-discussion-other-questions/710212-vba-code-convert-excel-pdf-email-attachment.html
    "
    that does more or less what i want in my VBA but implementing it into my Current VBA is no easy feat,
    would anyone be so kind as to help me piece it together... i would like the Macro to in addition send the saved pdf to a specific Recipient / s with a selected outlook account and specific signature in my case called "Nexus"

    Here is my current Macro, it works great at saving the PDF and sends a mail, but no attachment and no specific account or sig... please help me...
    Code:
    Option Explicit
    
    Private Const CSIDL_DESKTOP = &H0
    Private Type EMID
        cb As Long
        abID As Byte
    
    
    End Type
        Private Type ITEMIDLIST
    
    
    mkid As EMID
        End Type
    
    
    Private Declare Function SHGetSpecialFolderLocation Lib "shell32.dll" _
        (ByVal hwndOwner As Long, ByVal nFolder As Long, pidl As ITEMIDLIST) As Long
    
    
    Private Declare Function SHGetPathFromIDList Lib "shell32.dll" Alias _
        "SHGetPathFromIDListA" (ByVal pidl As Long, ByVal pszPath As String) As Long
    Private Function GetSpecialfolder(CSIDL As Long) As String
    
    
    Dim r As Long, Path$
    Dim IDL As ITEMIDLIST
    
    
            'Get the special folder
            r = SHGetSpecialFolderLocation(100, CSIDL, IDL)
            If r = 0 Then
    
    
            'Create a buffer
            Path$ = Space$(512)
    
    
    'Get the path from the IDList
    r = SHGetPathFromIDList(ByVal IDL.mkid.cb, ByVal Path$)
    
    
            'Remove the unnecessary chr$(0)'s
            GetSpecialfolder = Left$(Path, InStr(Path, Chr$(0)) - 1)
    
    
            Exit Function
    
    
        End If
    
    
    GetSpecialfolder = ""
    
    
    End Function
    Sub SaveIt()
      
        On Error Resume Next 'In case it already exists
    MkDir GetSpecialfolder(CSIDL_DESKTOP) & "\Famous_Brands" & "\" & Range("C7").Value
        
        ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False
    
    
        Dim Filename As String
        Dim Path As String
        Dim i As Integer
        Dim Mail_Object
        Dim Email_Subject
        Dim o As Variant
        
        Filename = Format(Date, "yyyy_mm_dd") & "_" & Range("J7").Value & "_" & Range("J8").Value
        Path = CreateObject("Wscript.Shell").SpecialFolders("Desktop")
        
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:=Path & "\Famous_Brands" & "\" & Range("C7").Value & "\" & Filename & ".Pdf", _
                Quality:=xlQualityStandard, _
                IgnorePrintAreas:=False, _
                OpenAfterPublish:=True
    
    
        ActiveWorkbook.SaveAs Filename:=Path & "\Famous_Brands" & "\" & "Complete_Job_Card_2018", _
                                FileFormat:=xlOpenXMLTemplateMacroEnabled, _
                                Password:="", _
                                WriteResPassword:="", _
                                ReadOnlyRecommended:=False, _
                                CreateBackup:=False
                                                          
            ActiveWorkbook.SaveAs Filename:=Path & "\" & "Complete_Job_Card_2018", _
                                FileFormat:=xlOpenXMLTemplateMacroEnabled, _
                                Password:="", _
                                WriteResPassword:="", _
                                ReadOnlyRecommended:=False, _
                                CreateBackup:=False
                                            
                Set Mail_Object = CreateObject("Outlook.Application")
            With Mail_Object.CreateItem(o)
                .Subject = "Famous Brands Repair Job Card" ' CHANGE TO SUIT
                .To = "receptionpta@nexusgroup.co.za" 'CHANGE TO SUIT
                .Body = "Machine Repaired and Ready for collection or courier." & Chr(13) & Chr(13) & "Regards," & Chr(13) & "Werner Johan Slabbert" & Chr(13) & "Nexus Technical" 'Change comments to suit
                .Attachments.Add Filename
                .Send
        End With
            MsgBox "E-mail successfully sent", 64
            Application.DisplayAlerts = False
    Set Mail_Object = Nothing
    
    
     
    End Sub
    Veni, Vidi, VELCRO..
    I CAME , I SAW.......
    I GOT STUCK ! ! !

  2. #2
    New Member
    Join Date
    May 2017
    Location
    Time And Relative Dimension In Space
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to save as PDF and mail PDF to Specific recipient/s from Specific Account with Specific Signature

    Hi Werner Slabbert,

    Could you go into a little more detail of what you would like to do in regards to:
    1.)Creating a PDF.
    a.)Is the PDF to be created in the current workbook?
    b.)Are there multiple worksheets to be created as PDF files?

    2.)Saving the PDF to a target location.
    a.)If the folder you are saving the files to located on your computer or on a server within your company?

    I am a bit confused by the first macro within your code and what you need it to do exactly.
    Is this code needed because you have a special set up in regards to your system or company server?

    3.)Emailing out the created PDF file(s)
    a.)Do you want to attached multiple files within a single email based on a parameter/criteria?

  3. #3
    Board Regular WERNER SLABBERT's Avatar
    Join Date
    Mar 2009
    Posts
    98
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to save as PDF and mail PDF to Specific recipient/s from Specific Account with Specific Signature

    Hi Eric H

    firstly, thank you so very much for taking a look at my post.
    the macro i posted auto saves a single sheet pdf in a folder on my desktop called "Famous_Brands" and then if the sub does not exist creates a sub folder from the specified cell in the sheet. it also prints said sheet to my default printer .

    now what i would just actually like to add in there is a bit that takes the already created and saved PDF and attaches it to an email to a specific recipient /s from a specific email account with a signature called "Nexus" ( already created in outlook)

    as for the confused bit in your post, in all honesty i would not actually be able to tell you if i need all that, but i am too **** scared to fiddle with it... like i said the macro should do the following:
    1. create PDF from single worksheet
    2. save said pdf in a sub folder inside the designated folder Famous_Brands with sub folder name in designated cell.
    3. print said worksheet straight to my default printer
    4. save a copy of the original workbook in two locations: Desktop & Documents
    5. ( now this is where my actual struggle starts) attach a copy of saved PDF to an email
    6. enter a recipient
    7. enter a signature called "Nexus"
    8. and send this mail from a designated account called "service*****@******.co.za"

    i do hope this clarifies my request somewhat..

    Regards
    Werner

    Quote Originally Posted by Eric H View Post
    Hi Werner Slabbert,

    Could you go into a little more detail of what you would like to do in regards to:
    1.)Creating a PDF.
    a.)Is the PDF to be created in the current workbook?
    b.)Are there multiple worksheets to be created as PDF files?

    2.)Saving the PDF to a target location.
    a.)If the folder you are saving the files to located on your computer or on a server within your company?

    I am a bit confused by the first macro within your code and what you need it to do exactly.
    Is this code needed because you have a special set up in regards to your system or company server?

    3.)Emailing out the created PDF file(s)
    a.)Do you want to attached multiple files within a single email based on a parameter/criteria?
    Veni, Vidi, VELCRO..
    I CAME , I SAW.......
    I GOT STUCK ! ! !

  4. #4
    New Member
    Join Date
    May 2017
    Location
    Time And Relative Dimension In Space
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to save as PDF and mail PDF to Specific recipient/s from Specific Account with Specific Signature

    Hi Werner,

    I am very sorry for the delay in my response I have had several unexpected issues come up.

    After building out the code module to accomplish your task I realized it might be a bit much to post here but I will post each macro and function I have created for your task.
    That being said I have a dropbox folder link I can post so you might download the whole workbook code included.

    I have created a Excel workbook with a VBA module containing macros that I believe will help you with accomplish the goal of:
    1.)Allow a user to choose what email account to send a email from out of a selection all available email accounts present in outlook.
    2.)Allow the user to choose which signature they would like to use from all signatures present in outlooks signatures folder.
    3.)Attach a file chosen by the user for any folder on your computer.
    a.)From ether a file path and file name string of text or
    b.)From a file chosen using the file dialog picker window.

    I have also included a comedic sample email message as part of the code to demonstrate what the end product will look like.

  5. #5
    New Member
    Join Date
    May 2017
    Location
    Time And Relative Dimension In Space
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to save as PDF and mail PDF to Specific recipient/s from Specific Account with Specific Signature

    Code:
    Option Explicit
    
    'Internet References & Inspirations
    
    'Use the mail account you want in your mail macro
    'https://www.rondebruin.nl/win/s1/outlook/account.htm
    
    'Insert Outlook Signature in mail
    'https://www.rondebruin.nl/win/s1/outlook/signature.htm
    
    'Sending Emails from another Outlook Account
    'https://www.youtube.com/watch?v=5SJWUusgzB0&list=PLNIs-AWhQzckr8Dgmgb3akx_gFMnpxTN5&index=44&t=0s
    
    Dim OutApp As Outlook.Application
    
    Public Sub Send_Mail_From_Other_Account()
    'This sub-procedure will do the following:
    
    'Note: This sub-procedure does require a reference the outlook object library to work.
    
        Dim ol_Mail_Item As Outlook.MailItem
        Dim str_User_Choosen_Email_Account As String
        Dim str_User_Choosen_Signature As String
        Dim str_User_Created_Email_Body As String
        Dim str_File_Path_and_File_Name_User_Choosen As String
        Dim vba_Result As VbMsgBoxResult
    
    '01.)Set a reference to the outlook object library.
        Set OutApp = _
            Outlook.Application
    
    '02.)Set a reference the outlook mail item.
        Set ol_Mail_Item = OutApp.Createitem(olMailItem)
    
    '03.)Use the function display all outlook accounts to allow the user to choose the outlook account they wish to use to send the email.
        Let str_User_Choosen_Email_Account = _
            fn_Display_Outlook_Accounts
    
    '04.)Use the function user created email body to create the body of the email message.
    '    This can be changed to display whatever message the user would like.
        Let str_User_Created_Email_Body = _
            fn_User_Created_Email_Body
    
    '05.)Use the function user choosen signature to allow the user to choose what signature they would like to use.
    '    If you know the name of the signature that you would like to use simply type it in for a parameter passed to the function.
        Let str_User_Choosen_Signature = _
            fn_User_Choosen_Signature()
        
    '06.)Populate the mail item with the needed information.
        With ol_Mail_Item
    
    '   a.)This line of code is used to access the user choosen email account.
            .SendUsingAccount = _
                OutApp.Session.Accounts(str_User_Choosen_Email_Account)
    
    '   b.)Populate the subject line of the email with whatever text the user would like.
            .Subject = _
                "Pizza Delivery Order Special Request"
    
    '   c.)Populate the send to line of the email with the required email information the user would like.
            .To = _
                "PizzaPlanetManagement@PizzaPlanet.com"
    
    '   d.)Populate the body of the email with the custom created message of the function and ...
    '      include the user choosen signature after the email body.
            .Body = _
                str_User_Created_Email_Body & vbCrLf & str_User_Choosen_Signature
                
    '07.)Prompt the user if they would like to attach a file.
            Let vba_Result = _
                MsgBox(Prompt:="Would you like to attach a file.", _
                       Buttons:=vbYesNo, _
                       Title:="Choose a file")
    
    '   a.)If the user has choosen yes they want to attach a file then do the following.
            If vba_Result = vbYes Then
            
    '   b.)Attach a file that the user has choosen.
    '       1.)Populate the string variable str_File_Path_and_File_Name_User_Choosen with the function value.
                Let str_File_Path_and_File_Name_User_Choosen = fn_Attach_User_Choosen_File()
            
    '       2.)Attach the file to the email.
                .Attachments.Add Source:=str_File_Path_and_File_Name_User_Choosen
                
           End If
    
    '06.)Return the step number 6.
    '   e.)Display the email message.
            .Display
    
    '08.)Prompt the user if they would like to send the email.
    '    This is to allow the user to make any manual changes to the email before it is sent.
    
        Let vba_Result = MsgBox( _
            Prompt:="Are you ready to send the email?", _
            Buttons:=vbYesNo, _
            Title:="Please review the email.")
    
    '09.)Evaluate the results of the user's message box selection.
            If vba_Result = vbNo Then
    '   a.)If the user has choosen "No" form the message box option display the email cancellation message to the user.
    
    '   b.)Ensure that this workbook is active to display the message box to the user.
            ThisWorkbook.Activate
    
    '   c.)Display the message box of user cancellation to the user.
            MsgBox _
                Prompt:="Email Macro Cancelled", _
                Title:="User Cancelled Email Macro"
    
    '   d.)Discard the created email.
            .Close olDiscard
                
            ElseIf vba_Result = vbYes Then
    '   b.)If the user has choosen "Yes" from the message box option then send the email and display email sent message to the user.
    
    '       1.)Send the email.
            .Send
    
    '       2.)Display message box to the user that the email was sent.
            MsgBox _
                Prompt:="The email has been sent.", _
                Title:="Email Sent Message"
                
            End If
    
        End With
        
    End Sub
    Code:
    Public Function fn_Display_Outlook_Accounts() As String
    'This function will do the following:
    
    'The user will be presented with an input box that contains all of the email accounts within the outlook manager...
    'he or she will then choose one via numeric value form the message within the input box.
    
        Dim ol_Account As Outlook.Account
        Dim lng_Row_Inc As Long
        Dim str_Input_Box_Msg As String
        Dim InputBox_Choice As Long
        Dim ar_Temp As Variant
        Dim str_User_Choosen_Account As String
    
    '01.)Redimension the size of the array to be the same row number as the number of email accounts.
        ReDim ar_Temp(1 To OutApp.Session.Accounts.Count, 1 To 2)
    
    '02.)Let the long data type variable "lng_Row_Inc" be equal to one.
        Let lng_Row_Inc = 1
    
    '03.)Assign the first line of the string data type variable  "str_Input_Box_Msg" to be the string "Acct Numeric Value - Account  Name".
        str_Input_Box_Msg = _
            str_Input_Box_Msg & "Acct Numeric Value - Account Name" & vbCrLf
            
    '04.)Cycle through all of the outlook accounts present.
        For Each ol_Account In OutApp.Session.Accounts
    
    '   a.)Populate the array column position one with the numeric value for the email account within the array.
            ar_Temp(lng_Row_Inc, 1) = _
                lng_Row_Inc
    
    '   b.)Populate the array column position two with the email account display name.
            ar_Temp(lng_Row_Inc, 2) = _
                OutApp.Session.Accounts.Item(lng_Row_Inc)
    
    '   c.)Add the account numeric value and display name to the string data type variable "str_Input_Box_Msg"
            str_Input_Box_Msg = _
                str_Input_Box_Msg & "(" & lng_Row_Inc & ") - " & ol_Account.DisplayName & vbCrLf _
    
    '   d.)Increase the long data type variable "lng_Row_Inc" by one.
            lng_Row_Inc = lng_Row_Inc + 1
            
        Next ol_Account
    
    '05.)Populate the InputBox parameter:
    '   a.)Title with "Choose a account number."
    '   b.)Prompt with the string data type variable "str_Input_Box"
    '   c.)Default with value as zero.
        Let InputBox_Choice = _
            VBA.Interaction.InputBox( _
                Prompt:=str_Input_Box_Msg, _
                Title:="Choose a account number.", _
                Default:=0)
    
    '06.)Pass the numerica value the user has choosen to the string data  type variable "str_User_Choosen_Account" which will pull the...
    '    email account display name from the array of email accounts.
        Let str_User_Choosen_Account = ar_Temp(InputBox_Choice, 2)
    
    '07.)Pass the user choosen variable back to the function.
        fn_Display_Outlook_Accounts = str_User_Choosen_Account
        
    End Function
    Code:
    Private Function fn_User_Choosen_Signature(Optional ByRef  User_Provided_Signature_Name As String = VBA.Constants.vbNullString) As  String
    'This function will do the following:
        
        Dim str_Signature_Path As String
        Dim str_Signature As String
        
        Dim ar_Temp As Variant, ar_Row_Inc As Long
        Dim str_Input_Box_Msg As String
        Dim lng_Inputbox_Choice As Long
        
    '01.)Search for the folder in which outlook signatures are stored.
        If User_Provided_Signature_Name <> VBA.Constants.vbNullString Then
    
    '02.)If the user has passed the desired file name for the signature file  to this function then build the full file path for the file.
            Let str_Signature_Path = _
                Environ("appdata") & "\Microsoft\Signatures" & User_Provided_Signature_Name & ".txt"
        
        ElseIf User_Provided_Signature_Name = VBA.Constants.vbNullString Then
    '03.)If the user has not passed a file name for the desired signature  file to this function then search out all available signature...
    '    text files and return the results in a inputbox message to allow the user to choose the desired signature.
    
    '   a.)Retrieve an array of signature files that are text files only from the target folder with numeric values to choose from.
            Let ar_Temp = fn_Retrieve_Array_of_Signature_Files(Environ("appdata") & "\Microsoft\Signatures")
         
    '   b.)Build the string data type message to be displayed in the inputbox begining with the title row information.
            str_Input_Box_Msg = _
                str_Input_Box_Msg & "Signature Value - Signature Name" & vbCrLf
    
    '   c.)Cycle through the signature files now present in the array and build the body of the inputbox message.
            For ar_Row_Inc = LBound(ar_Temp, 1) To UBound(ar_Temp, 1)
                
                str_Input_Box_Msg = _
                    str_Input_Box_Msg & "(" & ar_Row_Inc & ") - " & ar_Temp(ar_Row_Inc, 2) & vbCrLf _
    
            Next ar_Row_Inc
            
    '04.)Present the user with a InputBox with the signatures that are available for them to choose.
    '   a.)Populate the InputBox parameter:
    '       1.)Title with "Choose a signature number."
    '       2.)Prompt with the string data type variable "str_Input_Box"
    '       3.)Default with value as zero.
            Let lng_Inputbox_Choice = _
                VBA.Interaction.InputBox( _
                    Prompt:=str_Input_Box_Msg, _
                    Title:="Choose a signature number.", _
                    Default:=0)
                    
    '   b.)Use the numeric value that the user has selected to retrieve the  desired signature file from the array of signature files.
            Let str_Signature = _
                ar_Temp(lng_Inputbox_Choice, 2)
            
    '   c.)Create the full file path string to be used.
            Let str_Signature_Path = _
                Environ("appdata") & "\Microsoft\Signatures" & str_Signature
            
        End If
        
    '05.)Ensure that the file the user has choosen exists in the targeted folder.
        If Dir(str_Signature_Path) <> VBA.Constants.vbNullString Then
    
    '   a.)If the file is found then proceed to the function that will retrieve the data from the signature text file.
            Let str_Signature = _
                GetBoiler(str_Signature_Path)
    
        ElseIf Dir(str_Signature_Path) = VBA.Constants.vbNullString Then
    '   b.)If the user choosen signature file does not exist then return an empty string.
            Let str_Signature = VBA.Constants.vbNullString
            
        End If
    
    '06.)Pass the signature file string data back to the function.
        fn_User_Choosen_Signature = _
            str_Signature
    
    End Function
    Code:
    Private Function fn_Retrieve_Array_of_Signature_Files(ByRef Signatures_Folder_Path As String) As Variant
    'This function will do the following:
        
        Dim str_File_Name As String
        Dim lng_Signature_File_Count As Long
        Dim ar_Temp As Variant
    
    '01.)Use the user provided folder path to target the folder in which outlook signatures are stored.
        Let str_File_Name = VBA.FileSystem.Dir( _
            PathName:=Signatures_Folder_Path & "*.txt")
    
    '02.)Cycle through all text files found with the targeted folder and count them.
        While str_File_Name <> VBA.Constants.vbNullString
            lng_Signature_File_Count = _
                lng_Signature_File_Count + 1
    
    '   a.)To prevent an endless loop only search the target folder for  files matching the target criteria and once there are no more files the  While loop will end.
            Let str_File_Name = VBA.FileSystem.Dir()
        Wend
    
    '03.)Redimension the array to hold the signature file names tobe the  correct size to match how many signatures text files are present.
        ReDim ar_Temp(1 To lng_Signature_File_Count, 1 To 2)
    
    '04.)Reset the variable used to count the number of files present to be one.
        Let lng_Signature_File_Count = 1
    
    '05.)Reset the user provided folder path to target the folder in which outlook signatures are stored.
        Let str_File_Name = VBA.FileSystem.Dir( _
            PathName:=Signatures_Folder_Path & "*.txt")
    
    '06.)Cycle through the files again to retrieve the needed signature file names.
        While str_File_Name <> VBA.Constants.vbNullString
    
    '   a.)Populate the array with the needed information.
    '       The first column position of the array with house the numeric value associated with each signature file name.
            ar_Temp(lng_Signature_File_Count, 1) = _
                lng_Signature_File_Count
    
    '       The second column position of the array will house the signature file name.
            ar_Temp(lng_Signature_File_Count, 2) = _
                str_File_Name
                
    '       Increase the counting variable by one to continue populating the array.
            lng_Signature_File_Count = _
                lng_Signature_File_Count + 1
                
    '   b.)To prevent an endless loop only search the target folder for  files matching the target criteria and once there are no more files the  While loop will end.
            Let str_File_Name = _
                VBA.FileSystem.Dir()
        Wend
    
    '07.)Pass the array of signature file information back to the function.
        Let fn_Retrieve_Array_of_Signature_Files = _
            ar_Temp
        
    End Function
    Last edited by Fluff; Jul 11th, 2019 at 03:56 PM. Reason: Added code tags

  6. #6
    New Member
    Join Date
    May 2017
    Location
    Time And Relative Dimension In Space
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to save as PDF and mail PDF to Specific recipient/s from Specific Account with Specific Signature

    Code:
    Private Function GetBoiler(ByVal sFile As String) As String
    'Internet reference:
    'https://www.rondebruin.nl/win/s1/outlook/signature.htm
    '**** Kusleika
    
        Dim fso As Object
        Dim ts As Object
        Set fso = CreateObject("Scripting.FileSystemObject")
        Set ts = fso.GetFile(sFile).OpenAsTextStream(1, -2)
        GetBoiler = ts.readall
        ts.Close
        
    End Function
    Code:
    Private Function fn_User_Created_Email_Body() As String
    'This function will do the following:
    
        Dim Str_Email_Body As String
        
    '01.)Populate a string data type variable with the information you wish  to be diplayed as the body of the email one line of text at a time.
    
    '   a.)Body Text line one (General Greeting) with a empty line between this this line and the main email body text.
        Let Str_Email_Body = _
            Str_Email_Body & "Dear Pizza Planet Management," & vbCrLf & vbCrLf
    
    '   b.)Body text line two.
        Let Str_Email_Body = _
            Str_Email_Body & "This Buzz Lightyear of star command I  would like to make intergalactic pizza delivery order." & vbCrLf
    
    '   b.)Body text line three.
        Let Str_Email_Body = _
            Str_Email_Body & "I assure you this is not a prank, I am a  space ranger stationed on the far side of the Milky Way galaxy." &  vbCrLf
        
    '   b.)Body text line four.
        Let Str_Email_Body = _
            Str_Email_Body & "I am in desperate need of a large  pepperoni pizza with mushrooms, make that extra mushrooms!" & vbCrLf
        
    '   b.)Body text line five.
        Let Str_Email_Body = _
            Str_Email_Body & "If you accept this long and perilous mission I will be forever in your debt." & vbCrLf
        
    '   b.)Body text line six.
        Let Str_Email_Body = _
            Str_Email_Body & "Also, I realize that is may take you three thousand years to get to my location..." & vbCrLf
        
    '   b.)Body text line seven.
        Let Str_Email_Body = _
            Str_Email_Body & "but does the ""If your pizza is not  delivered in thirty minutes or less it's free!"" deal still apply?"  & vbCrLf
    
    '   b.)Body text line eight.
        Let Str_Email_Body = _
            Str_Email_Body & "" & vbCrLf
        
    '02.)With all lines of text being committed to the string data type variable return the variable to the function.
        Let fn_User_Created_Email_Body = _
            Str_Email_Body
        
    End Function
    Code:
    Private Function fn_Attach_User_Choosen_File(Optional ByRef  File_Path_and_File_Name As String = VBA.Constants.vbNullString) As  String
    'This function will allow the user to navigate to and choose a file to be attached to the email...
    'if they have not passed a file path and file name to the function already.
        
    Restart_Function:
        
        Dim dlg_File_Dialog As FileDialog
        Dim lng_Dialog As Long
        Dim str_File_Found As String
        Dim bln_File_Found As Boolean
    
    '01.)Determine if the user has passed a file path and file name to the function.
    
        If File_Path_and_File_Name = VBA.Constants.vbNullString Then
    '02.)If the user has not passed a file path and file name to the function then prompt the user to navigate to and choose a file.
    '   a.)Set the file dialog variable reference.
            Set dlg_File_Dialog = _
                Application.FileDialog(msoFileDialogFilePicker)
    
    '   b.)Populate the file dialog title with this text.
            Let dlg_File_Dialog.Title = _
                "Please choose a file."
    
    '   c.)Display a message box to the user that they must choose a file to attach.
            MsgBox Prompt:="Please choose a file."
    
    '   c.)Display/Show the file dialog box
            Let lng_Dialog = dlg_File_Dialog.Show
    
    '   d.)Determine if the user has exited the file dialog box without choosing a file.
            If lng_Dialog = 0 Then
    '   e.)If the user has not choosen a file to attach the prompt them they must choose a file.
                VBA.Interaction.MsgBox _
                    Prompt:="You are required to choose a file.", _
                    Title:="Please Choose a File."
                    
    '   f.)Restart the function
                GoTo Restart_Function
                
            Else
    '   g.)If the user has choosen a file than return the first file choose from the array of choosen files.
                Let str_File_Found = dlg_File_Dialog.SelectedItems(1)
                
            End If
            
    '   h.)Return the file path and file name to the function.
            Let fn_Attach_User_Choosen_File = str_File_Found
    
    '   i.)No further action is needed exit the function.
            Exit Function
            
        ElseIf File_Path_and_File_Name <> VBA.Constants.vbNullString Then
    '03.)The user has provided a file path and file name to be attached to the email.
    
    '   a.)Ensure that the targeted file exists in the targeted folder. (Good practice to ensure no errors.
    '       1.)If the file exists then return the file path and file name to the function and exit the function.
            Let bln_File_Found = fn_Does_File_Exist(File_Path_and_File_Name)
    
    '   b.)If the file exists than return the file path and file name to the function.
            If bln_File_Found = True Then
                
    '   c.)Return the file path and file name to the function.
                fn_Attach_User_Choosen_File = File_Path_and_File_Name
    
    '   e.)No further action is required exit the funciton.
                Exit Function
                
            End If
            
        End If
        
    End Function
    Code:
    Private Function fn_Does_File_Exist(ByRef File_Path_and_File_Name As String) As Boolean
    'This function will do the following:
    
        Dim str_File_Found As String
        Dim bln_File_Found As Boolean
    
    '01.)Test file path and file name in order to determine if the files exists in the provided folder.
        Let str_File_Found = _
            VBA.FileSystem.Dir(PathName:=File_Path_and_File_Name, _
                            Attributes:=vbNormal)
    
    '02.)If the returned variable is not a null string then the file exists.
        If str_File_Found <> VBA.Constants.vbNullString Then
    
    '   a.)Change the boolean data type variable bln_File_Found to true.
            bln_File_Found = True
            
    '   b.)Return true to the function.
            fn_Does_File_Exist = bln_File_Found
    
    '   c.)No futher action is required exit the function.
            Exit Function
            
    '03.)if the returned variable is a null string then the file does not exist.
        ElseIf str_File_Found = VBA.Constants.vbNullString Then
    
    '   a.)Notify the user that the file does not exist in the target folder.
            VBA.Interaction.MsgBox _
                Prompt:="The file does not exist " & vbCrLf & _
                        "in the targeted folder.", _
                Title:="File Does Not Exist!"
                
    '   b.)Return false to the function.
            fn_Does_File_Exist = False
            
    '   c.)No futher action is required exit the function.
            Exit Function
             
        End If
        
    End Function
    Last edited by Fluff; Jul 11th, 2019 at 03:57 PM.

  7. #7
    Board Regular WERNER SLABBERT's Avatar
    Join Date
    Mar 2009
    Posts
    98
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up Re: VBA to save as PDF and mail PDF to Specific recipient/s from Specific Account with Specific Signature

    Hi Eric

    not to worry about a late response you know what they say, beggars and choosers .... thank you so very very much for all the time and effort you have put into this, i owe you a considerable debt .
    if you would not mind giving me the link for the complete workbook please . that would simplify it alot.

    again i do not know how to thank you for the help. you are a MAMBA.


    Quote Originally Posted by Eric H View Post
    Hi Werner,

    I am very sorry for the delay in my response I have had several unexpected issues come up.

    After building out the code module to accomplish your task I realized it might be a bit much to post here but I will post each macro and function I have created for your task.
    That being said I have a dropbox folder link I can post so you might download the whole workbook code included.

    I have created a Excel workbook with a VBA module containing macros that I believe will help you with accomplish the goal of:
    1.)Allow a user to choose what email account to send a email from out of a selection all available email accounts present in outlook.
    2.)Allow the user to choose which signature they would like to use from all signatures present in outlooks signatures folder.
    3.)Attach a file chosen by the user for any folder on your computer.
    a.)From ether a file path and file name string of text or
    b.)From a file chosen using the file dialog picker window.

    I have also included a comedic sample email message as part of the code to demonstrate what the end product will look like.
    Veni, Vidi, VELCRO..
    I CAME , I SAW.......
    I GOT STUCK ! ! !

  8. #8
    New Member
    Join Date
    May 2017
    Location
    Time And Relative Dimension In Space
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to save as PDF and mail PDF to Specific recipient/s from Specific Account with Specific Signature

    I should have ask this before but I wrote the program to use office 2013.
    What version do you have?

    https://www.dropbox.com/sh/chawlzqtx...0qcEY55Pa?dl=0

  9. #9
    Board Regular WERNER SLABBERT's Avatar
    Join Date
    Mar 2009
    Posts
    98
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to save as PDF and mail PDF to Specific recipient/s from Specific Account with Specific Signature

    Hi
    i'm running 2016. would it help if i send you a copy of my original workbook?

    https://mega.nz/#!UM9H0azQ!mrgw1ktSx..._3TduSfPyf80yw

    Quote Originally Posted by Eric H View Post
    I should have ask this before but I wrote the program to use office 2013.
    What version do you have?

    https://www.dropbox.com/sh/chawlzqtx...0qcEY55Pa?dl=0
    Last edited by WERNER SLABBERT; Today at 01:58 AM. Reason: Added Link
    Veni, Vidi, VELCRO..
    I CAME , I SAW.......
    I GOT STUCK ! ! !

  10. #10
    New Member
    Join Date
    May 2017
    Location
    Time And Relative Dimension In Space
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to save as PDF and mail PDF to Specific recipient/s from Specific Account with Specific Signature

    I have downloaded your project and I will look it over asap.
    Please let me if the Excel workbook I created is working for your task. I would be happy to adapt it to better suit your needs.

    Eric

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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