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

[WERNER SLABBERT]

Hi there ....
i recently found this thread
"

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...

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

 

[Eric H]

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?

 

[WERNER SLABBERT]

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

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?

 

[Eric H]

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.

 

[Eric H]

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

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

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

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

 

[Eric H]

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

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

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

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

 

[WERNER SLABBERT]

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.

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.

 

[Eric H]

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/chawlzqtxmv1tbn/AADeYZqHcBjcaN340qcEY55Pa?dl=0

 

[WERNER SLABBERT]

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

https://mega.nz/#!UM9H0azQ!mrgw1ktSxK1t0iw7CSKMHih2APr8p_3TduSfPyf80yw

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/chawlzqtxmv1tbn/AADeYZqHcBjcaN340qcEY55Pa?dl=0

 

[Eric H]

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