VBA - Retrieve emails Attachment & Download

Copper Contributor

Hi Everyone,

I have a big task and I don't know if this can be done through VBA.  Let me explain what I need to be done.

1.  I have several clients that I setup an email address through Exchange server (not my personal outlook). with a password.

2. Then I have several customers that sending email attachment for that particular client.  Let say one of the client’s name Troy Corporation and the email will be email address removed for privacy reasons.

The customer’s name is: Daybreak Fast, FedEx Freight, R&L Carriers, and TRN Logistics.  All the customers are sending email attachment into Troy Corporation mailbox. Side Note: (sometimes I have different customer send the attached file. Let say this I have this week Mr. email address removed for privacy reasons and next week I have Mrs. email address removed for privacy reasons I need the program to check for both emails to see if one of them send me a file.

What I need is to do is login to mailbox (Troy Corporation) retrieve each email with the attached file for each customer, then download them to my network folder.  If I did not receive an attachment for any of the customer to send them a second request with a note on the body "we did not receive your file" something like that.

3. So, when I login for second time to retrieve the remaining attached file. But if I did receive the email attachment either on the first or the second attempt, the customer SHOULD NOT RECEIVE a second request because I did receive their file.

I do have more after all the files are download to my network folder.  But I want to know if this can be done through VBA. OR if cannot be done through VBA, which program do you think is the best to be done with?

Thank you an advanced!

17 Replies

@TTC-BlueHill1 

What you are describing can be achieved using VBA, particularly if you are using Outlook as your email client. Here is a high-level overview of how you can approach this task:

  1. Set up Outlook Application Object: You will need to create an instance of the Outlook Application object in your VBA code to interact with Outlook.
  2. Authenticate and Access Mailbox: You will need to authenticate and access the mailbox of the client (e.g., Troy Corporation) using either the client's credentials or your credentials if you have access.
  3. Retrieve Emails and Attachments: Use VBA code to retrieve emails from the client's mailbox. You can loop through the emails, check for specific sender email addresses (e.g., Daybreak Fast, FedEx Freight, etc.), and download any attachments associated with those emails to a specified network folder.
  4. Send Second Request if Attachment Not Received: If an attachment is not found for a particular customer, you can send a second request email to the sender notifying them that the attachment was not received. You can compose this email using VBA and send it from the client's mailbox.
  5. Handle Second Retrieval: After some time has passed, you can run the VBA code again to retrieve any remaining attachments that were not received during the initial retrieval.

Here is a simplified example of what the VBA code might look like:

Vba code is untested, please backup your file first.

Sub RetrieveAttachments()
    Dim olApp As Outlook.Application
    Dim olNs As Outlook.NameSpace
    Dim olFolder As Outlook.MAPIFolder
    Dim olMail As Outlook.MailItem
    Dim olAttachment As Outlook.Attachment
    Dim strFolderPath As String
    
    ' Initialize Outlook Application
    Set olApp = New Outlook.Application
    Set olNs = olApp.GetNamespace("MAPI")
    
    ' Access the client's mailbox folder
    Set olFolder = olNs.Folders("Troy Corporation").Folders("Inbox")
    
    ' Specify the network folder path to save attachments
    strFolderPath = "\\network\folder\path\"
    
    ' Loop through each email in the folder
    For Each olMail In olFolder.Items
        ' Check if the email is from a specific sender
        If olMail.SenderEmailAddress = "email address removed for privacy reasons" Then
            ' Loop through attachments and save them to the network folder
            For Each olAttachment In olMail.Attachments
                olAttachment.SaveAsFile strFolderPath & olAttachment.FileName
            Next olAttachment
        End If
    Next olMail
    
    ' Cleanup
    Set olAttachment = Nothing
    Set olMail = Nothing
    Set olFolder = Nothing
    Set olNs = Nothing
    Set olApp = Nothing
End Sub

This is just a basic example to get you started. You will need to expand and customize the code to fit your specific requirements, such as handling multiple senders, sending second requests, etc. The text, steps and vba code were created with the help of AI.

 

My answers are voluntary and without guarantee!

 

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.

@NikolinoDE
Thank you for the great start. As you mentioned that I need to create an instance of the outlook application object. Since I am new to VBA code and I don't know that much about it. I was hoping to give me something from start to finish so all I need to do is to replace a minor code such the path of the file and run the VBA code.
Question: Can I run your code as is by changing the file path?

@TTC-BlueHill1 

You can run the provided code with minor modifications to the file path.

Here is how you can do it:

  1. Set Up Outlook Application Object: The code already initializes the Outlook Application object, so you don't need to make any changes here.
  2. Authenticate and Access Mailbox: The code assumes that you have access to the mailbox of the client "Troy Corporation" and the folder "Inbox". If you have access to this mailbox, then you do not need to make any changes. However, if you need to access a different mailbox or folder, you will need to modify the code accordingly.
  3. Specify the Network Folder Path: You need to specify the network folder path where you want to save the attachments. Replace the placeholder "\network\folder\path" in the code with the actual path to your network folder. Make sure to use double backslashes "\" to escape the path separators.

Once you have made these changes, you can run the VBA code, and it will retrieve attachments from the specified mailbox and save them to the network folder you've specified.

 

Here is a step-by-step guide to running the VBA code:

Step 1: Open Excel and Access the Visual Basic for Applications (VBA) Editor

  1. Open Microsoft Excel.
  2. Press Alt + F11 to open the Visual Basic for Applications (VBA) editor.

Step 2: Insert a New Module

  1. In the VBA editor, go to the "Insert" menu and choose "Module." This will insert a new module into the project.

Step 3: Copy and Paste the VBA Code

  1. Copy the provided VBA code (the code in the previous response).
  2. Paste the code into the blank module you inserted in Step 2.

Step 4: Modify the File Path

  1. In the VBA code, locate the line that specifies the network folder path to save attachments:

strFolderPath = "\\network\folder\path\"

  1. Replace "\network\folder\path" with the actual path where you want to save the attachments.

Step 5: Save the Changes

  1. Close the VBA editor and return to your Excel workbook.
  2. Save your Excel workbook to ensure that the changes are preserved.

Step 6: Run the VBA Code

  1. Press Alt + F8 to open the "Macro" dialog box in Excel.
  2. In the "Macro" dialog box, you should see the "RetrieveAttachments" macro listed.
  3. Select the "RetrieveAttachments" macro and click the "Run" button to execute the VBA code.

That's it! The VBA code will now run and attempt to retrieve attachments from the specified email sender(s) in the Troy Corporation mailbox. Make sure you have Outlook installed and configured on your system for this code to work properly.

Good morning @NikoninoDE
I am getting an error on the first code "olApp As Outlook.Application and the error 'compile error: user-defined type not defined'. I try to paste the screenshot in the reply, but the forum won't let me and I know don't why you can't to that.
Also, I have a lot read messages in the inbox, but I only want to download just the 'unread' messages. Can the code skip all the read messages and bring only the 'unread' messages and download the attachments?
Also to enter to the client mailbox. I use the user name which is Troy email address and the password.
I don't see where in the code to enter both user name and the password.
You might look into a solution with PowerAutomate, if it's available. There are flows that will download attachments. May have to modify them a bit for your task.

Thank you for your input sir! The VBA is 100x faster than PowerAutomate. If this task can be accomplished in vba code I rather go to that route. The gentleman told me that it can be done in vba. I just need to modify the code a little bit for my tasks. If there's no chance that cannot be done in vba, then, I will think with something else.

@TTC-BlueHill1 

It seems like you are encountering a couple of issues. Let us address them step by step:

1. 'User-defined type not defined' error: This error typically occurs when the necessary Outlook object library reference is not added to your VBA project. To resolve this, you need to ensure that the Microsoft Outlook Object Library is referenced in your VBA project. Here's how you can do it:

    • In the VBA editor, go to the "Tools" menu and select "References."
    • In the References dialog box, find and check "Microsoft Outlook xx.x Object Library" (where xx.x represents the version number).
    • Click "OK" to save the changes.

After adding the reference, the 'Outlook' objects should be recognized without errors.

2. Downloading only unread messages: To modify the code to download only unread messages, you can add a condition to check if the email is unread before processing it. Here's how you can do it:

Vba code is untested please backup your file

 

Sub RetrieveAttachments()
    Dim olApp As Outlook.Application
    Dim olNs As Outlook.NameSpace
    Dim olFolder As Outlook.MAPIFolder
    Dim olMail As Outlook.MailItem
    Dim olAttachment As Outlook.Attachment
    Dim strFolderPath As String
    
    ' Initialize Outlook Application
    Set olApp = New Outlook.Application
    Set olNs = olApp.GetNamespace("MAPI")
    
    ' Access the client's mailbox folder
    Set olFolder = olNs.Folders("Troy Corporation").Folders("Inbox")
    
    ' Specify the network folder path to save attachments
    strFolderPath = "\\network\folder\path\"
    
    ' Loop through each email in the folder
    For Each olMail In olFolder.Items
        ' Check if the email is unread and from a specific sender
        If olMail.UnRead And olMail.SenderEmailAddress = "email address removed for privacy reasons" Then
            ' Loop through attachments and save them to the network folder
            For Each olAttachment In olMail.Attachments
                olAttachment.SaveAsFile strFolderPath & olAttachment.FileName
            Next olAttachment
        End If
    Next olMail
    
    ' Cleanup
    Set olAttachment = Nothing
    Set olMail = Nothing
    Set olFolder = Nothing
    Set olNs = Nothing
    Set olApp = Nothing
End Sub

 

With this modification, the code will only process unread emails from the specified sender and download their attachments to the specified network folder. Make sure to replace "email address removed for privacy reasons" with the actual sender's email address.

3. Specifying username and password: If you are accessing an Exchange server mailbox using VBA, typically, it will use the credentials of the currently logged-in user. However, if you need to specify a different username and password, you might need to use different methods, such as Exchange Web Services (EWS) or Outlook REST API, which would involve more complex authentication mechanisms. For simplicity, the provided code assumes access using the currently logged-in user's credentials. The text, steps and code were created with the help of AI.

@TTC-BlueHill1 

Mr. Patrick2788 idea of using Power Automate is also very interesting.

The speed of execution can depend on various factors, including the complexity of the task, the efficiency of the implementation, and the environment in which the automation is running. Comparing Power Automate to VBA in terms of speed can be nuanced.

In summary, while VBA might have certain performance advantages for specific tasks executed locally, Power Automate offers advantages in terms of ease of development, scalability, and integration capabilities. The choice between the two depends on factors such as the complexity of the task, the need for integration with external services, and the user's familiarity with each platform.

For your specific case of retrieving email attachments from an Exchange server mailbox, i think both Power Automate and VBA can accomplish the task effectively.

But in the end, you are the one who has to decide which solution is best for your project.

 

Here is an small outline of how you can set up a flow in Power Automate to achieve your task:

  1. Trigger: Start with an appropriate trigger based on when you want the flow to run. Since you want to process emails, you can use the "When a new email arrives" trigger from the Outlook connector.
  2. Condition: Add a condition to filter out emails from specific senders or with specific subjects. In your case, you would filter emails based on the sender's email address.
  3. Get Attachments: Within the condition, use the "Get attachments" action to retrieve attachments from the email.
  4. Save Attachments: Use the "Create file" action from the OneDrive or SharePoint connector to save the attachments to a specified folder in your network.
  5. Send Email (Optional): Optionally, you can add an action to send a follow-up email to the sender if no attachment is found in the email.
  6. Handle Unread Emails (Optional): To handle only unread emails, you can add a condition at the beginning of the flow to check if the email is unread before processing it.

To create such a flow, follow these steps:

  1. Go to the Power Automate portal (https://flow.microsoft.com) and sign in.
  2. Click on "Create" in the top navigation bar and select "Automated cloud flow."
  3. Choose the appropriate trigger based on your requirements, such as "When a new email arrives" from Outlook.
  4. Configure the trigger by signing in to your Outlook account and specifying any additional parameters such as folder, subject, sender's email address, etc.
  5. Add a condition action and configure it to filter emails based on the sender's email address.
  6. Within the condition, add the "Get attachments" action and configure it to retrieve attachments from the email.
  7. Add the "Create file" action and configure it to save the attachments to a specified folder in your network.
  8. Optionally, add actions to send follow-up emails or perform other tasks based on your requirements.
  9. Save the flow and give it an appropriate name.
  10. Test the flow to ensure it's working as expected.

    The steps were processed using AI.

By following these steps, you can create a Power Automate flow to automatically download attachments from emails sent by specific senders and save them to a network folder.

Thank you both! I will explore both programs and decide which one is best for this project.
I will keep your guys posted on my progress.
Again million thanks!
What I need is to do is login to mailbox (Troy Corporation) retrieve each email with the attached file for each customer,

' Loop through each email in the folder
For Each olMail In olFolder.Items

I guess these codes above which intend to check all mails downloaded rather than saved on the remote Exchange server.
If you need to snyc frequently,one way is to use POP3 to receive email.
Alternatively,you can use an online form (i.e ms froms) to collect infomation which provide a list of all responses instead of individual emails.
Hello Sir,
I was able to go 'tool's and check in 'Microsoft Outlook 16.0 Object Library', but when I ran the code I got this error now "Run-time error - 2147221233 (8004010f). The attempted operation failed. An object could not be found".
Please let what need to be done for me to run this code.
Thanks again!

@NikolinoDE
I have the Microsoft Outlook 16.0 Object Library check in. But when I try to run the code, I got an error 'Run-time error '-2147221233 (8004010f)'" The attempted operation failed. An object could not be found'. Please let me know how I can fixed this so I rerun the code again.
Thanks again!

Hello,
I am still having issue on how to complete this task by using VBA code.
Hello sir,
I will want to try the vba code, but I am still having. Can you help?
I am getting this error below.
"Run-time error - 2147221233 (8004010f). The attempted operation failed. An object could not be found".
Issues with the registration of a COM object.
Maybe need reinstall outlook or win system

@TTC-BlueHill1 

The "Run-time error - 2147221233 (8004010f)" in Outlook typically indicates an issue with the Outlook profile or the way Outlook is configured. This error can occur for various reasons, including corrupted profiles, missing or inaccessible data files, or issues with the Outlook installation itself.

Here are some steps you can follow to troubleshoot and resolve this error:

1. Repair Outlook Profile

  • Open Outlook.
  • Go to File > Account Settings > Account Settings.
  • Select the email account causing the issue and click Repair.

2. Create a New Outlook Profile

  • Close Outlook.
  • Open the Control Panel and go to Mail (or Mail (32-bit)).
  • Click on Show Profiles.
  • Click Add to create a new profile and configure it with your email account.
  • Set the new profile as the default profile.

3. Check Data File Locations

  • Open Outlook.
  • Go to File > Account Settings > Account Settings.
  • Check the location of your data files under the Data Files tab.
  • Ensure that all data files are accessible and not corrupted.

4. Run Outlook with Resetnavpane

  • Close Outlook.
  • Press Win + R to open the Run dialog.
  • Type outlook.exe /resetnavpane and press Enter.
  • This command resets the Navigation Pane settings in Outlook.

5. Repair Office Installation

  • Open the Control Panel.
  • Go to Programs > Programs and Features.
  • Select Microsoft Office from the list and click Change.
  • Choose the Repair option and follow the prompts.

6. Update Outlook

  • Ensure that Outlook and Office are updated to the latest version.
  • Go to File > Office Account > Update Options > Update Now.

7. Check for Add-Ins

  • Sometimes, third-party add-ins can cause issues.
  • Open Outlook in Safe Mode by holding Ctrl while launching Outlook.
  • Disable add-ins by going to File > Options > Add-ins.
  • Manage COM Add-ins and disable unnecessary ones.

8. Check Internet Connectivity

  • Ensure that your internet connection is stable.
  • Sometimes, connectivity issues can cause errors in Outlook operations.

 

If none of that helps, then I recommend what was already recommended: a new installation.

 

Hope this information was helpful to you.