Forum Discussion
VBA - Retrieve emails Attachment & Download
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!
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:
- 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.
- 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.
- 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.
- 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.
- 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 SubThis 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?You can run the provided code with minor modifications to the file path.
Here is how you can do it:
- Set Up Outlook Application Object: The code already initializes the Outlook Application object, so you don't need to make any changes here.
- 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.
- 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
- Open Microsoft Excel.
- Press Alt + F11 to open the Visual Basic for Applications (VBA) editor.
Step 2: Insert a New Module
- 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
- Copy the provided VBA code (the code in the previous response).
- Paste the code into the blank module you inserted in Step 2.
Step 4: Modify the File Path
- In the VBA code, locate the line that specifies the network folder path to save attachments:
strFolderPath = "\\network\folder\path\"
- Replace "\network\folder\path" with the actual path where you want to save the attachments.
Step 5: Save the Changes
- Close the VBA editor and return to your Excel workbook.
- Save your Excel workbook to ensure that the changes are preserved.
Step 6: Run the VBA Code
- Press Alt + F8 to open the "Macro" dialog box in Excel.
- In the "Macro" dialog box, you should see the "RetrieveAttachments" macro listed.
- 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.
- 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.
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:
- 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.
- 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.
- Get Attachments: Within the condition, use the "Get attachments" action to retrieve attachments from the email.
- Save Attachments: Use the "Create file" action from the OneDrive or SharePoint connector to save the attachments to a specified folder in your network.
- 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.
- 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:
- Go to the Power Automate portal (https://flow.microsoft.com) and sign in.
- Click on "Create" in the top navigation bar and select "Automated cloud flow."
- Choose the appropriate trigger based on your requirements, such as "When a new email arrives" from Outlook.
- Configure the trigger by signing in to your Outlook account and specifying any additional parameters such as folder, subject, sender's email address, etc.
- Add a condition action and configure it to filter emails based on the sender's email address.
- Within the condition, add the "Get attachments" action and configure it to retrieve attachments from the email.
- Add the "Create file" action and configure it to save the attachments to a specified folder in your network.
- Optionally, add actions to send follow-up emails or perform other tasks based on your requirements.
- Save the flow and give it an appropriate name.
- 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.
- 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,
I am still having issue on how to complete this task by using VBA code.
