Send Mass Emails (Gmail or Outlook) From Excel Automatically, With Attachment Based on Excel List

okay you want to send emails based on a list in Microsoft Excel and that email needs to contain a specified attachment now previously I have created a video on this same topic and the solution I used there was a VBA macro I'll leave a link in the description of this video to that tutorial in this video we're not going to use a VBA macro we're going to use power automate so the way this is going to work is I'm going to select rows within this table and it will send an email to the specified recipient with the specified attachment and the attachments are stored in a folder on my OneDrive called certificates now your first step is to house your data in an Excel table and the way to do that is to click in any cell in your data and then go up to the insert tab on your ribbon and click on this table button click on OK down here and then give the table a name so I'm going to call this delegate list press enter to store the name now this file is saved on one drive so what I'm going to do now is go over to my Microsoft 365 account and create a power automate flow now if you don't know how to get to your power automate screen obviously you have to log into your Microsoft 365 account and then you'd click on this app launcher button and select power automate down here then you'd click on this create button and you'd select instant Cloud flow give your flow a name I'll call this email course certificates and then you need to select a trigger for your flow for us that's going to be for a selected row so I'm going to click on create so next I need to specify where I've saved this Excel file and for me that's on one drive for business and the document library is OneDrive the file I need to navigate to by clicking on this little folder button and it's in the courses folder so I click on this arrow button to show the contents of that folder and there's the file certificate list then I need to specify the table within that file that contains my data and that's the delegate list table now the next step is to get the flow to get all the files within this certificates folder so to do that I'm going to create a new step and I'm going to type in the search box list files in folder and there we have it list files of folder OneDrive for business now I need to provide a unique identifier for the folder so what I do is I navigate to the folder and this is the folder the certificates folder so now I need to Loop through each of the files of that folder and to do that I create another new step and this time we're creating control and then apply to each so next I need to select an output from the previous steps so to do that I'm going to click into this box then where it says search Dynamic content I'm going to search for value and then select it here so now I need to add another action and this action is going to be a condition action so I can see it there if you can't see it just type condition in the search box now what I want to say is if the attachment field so if we go back to my Excel spreadsheet we have an attachment field here that contains the name of each attachment if the attachment text is equal to the file name of one of the files in the certificates folder then I want to email a certificate the first thing I need to do is to get the file content for the files within my certificates folder now I type get file content in the search bar and you can see I have an action here get file content so I need to provide a unique identifier for the file I can do that just by clicking in that box and then choosing this ID field here so remember this is looping through each of the files within the certificates folder so then once I've got the file ID I can now send an email so I add an action and then you type email in the search box here you'll get some email options now whether you use Office 365 Outlook or say Gmail the process is the same I'm using Gmail so I'm going to click on that button and then you'd select send email version two will be the same for Outlook so in the two box you need to specify the email address that appears in the selected rows within your spreadsheet so to do that you click on ADD Dynamic content and then you want to select email formatted now for the subject we're going to include the recipient's name so we can pick that up again from this list so first name formatted comma is your certificate and then the body we're going to say hi and then the person's name again and then we'll say congratulations on completing the they will have the course name of course please find course certificate attached okay cheers Chester the next thing we need to do is attach the certificate to the email so you can see here I've got attachment name and attachments content now if you can't see those fields click on show Advanced options I think they show with Gmail by default but not for Outlook so we click in this attachment name field and we need to select the name of the file so we can do that down here and then attachment content you'd select file content okay so the other Branch doesn't need any actions so if we don't find a certificate in the certificates folder that matches the name in our Excel list we don't want to do anything so we've effectively completed our flow so we'll click on this save button and now we'll go back to Excel and see if we can get this flow to work get the flow to work in Excel you're going to need to download the power automate add-in now if you've already downloaded it it'll be on the data tab over here in a group called automation but if you haven't got that app just go to the insert tab on your ribbon and click on get add-ins and you would search for Power automate and then you need to add that to excel so if I go to data and then click on Flow it opens up this task pane on the right of my worksheet now if it's the first time we've used this add-in it will ask you to sign into your Microsoft account but once you've done so it will list the flows that are available for this workbook you can see we've got our email course certificates flow ready and waiting to be used so to use this flow I need to first of all select the rows that I want to send emails for and then I click on this little play button next to this flow name and then I click on continue then I click on run flow okay let's switch to Gmail and see if this has worked you'll notice that I'm sending all of the emails to myself okay so I'm in my Gmail account and here are the emails or click on this email congratulations on completing your Excel fundamentals course please find course certificate attached and there is the course certificate you can see I've also got one for Laureen okay that's all I wanted to cover in this particular video hopefully that's useful if it is please give me a thumbs up and subscribe and I'll see you next video foreign foreign foreign foreign

Channel: Chester Tugwell

Views: 8,958

Keywords: gmail excel, send email from excel automatically, create email list from excel column, how to send emails from excel using outlook, email from spreadsheet, how to send multiple emails from excel spreadsheet with attachment, excel send email with attachment, send emails from excel spreadsheet, send email from excel list with attachment, email list from excel to gmail, gmail mailing list from excel, send mass email from excel with attachment

Id: bpAqF8VhLhc

Length: 9min 58sec (598 seconds)

Published: Tue Sep 05 2023