VBA To Send Email from Excel with File as Attachment and Image in Mail body - Email Automation

foreign [Music] in this tutorial we are going to learn how to send automated email through Visual Basic application and Excel with attachment and image in Mill body so let's move to the demo file and see how it works and then we will create the same application from scratch so here you can see that uh this is the email automation uh application which has been developed in Excel and in this Excel workbook we have two different worksheets first one is home and the second one is email list so home is nothing but a user interface and some visuals you know background so that user can click on the send button to send the automated email an email list so this is a tabular information having three columns username email ID and status so while sending the email you just need to fill this username or and email ID and you'll you need to leave this status blank okay so suppose you just need to send 1000 emails so you will have to mention the username or the employee name you can say in this column A and then respective email ID in this column and after that you just need to come back to home worksheet and click on send button so it will start sending the email to each and every one and then the status will get updated to done done against every employee or the user list okay so this is a very simple app uh simple user interface and you do not need to worry about some other file and details and let's come to the support data basically so here this is our email automation file which I have already opened and what we are doing we are going to attach this service list that is the Excel file as an attachment and we will embed this the the data Labs image in the mail body so let me show you uh the outcome the output basically the the email out so this is this has this email has been generated by uh this application the email automation okay so the service list the Excel file will get attached okay and the data labs this image will be embedded in the mail body we are also using some hyperlink over here with the uh with the help of HTML and Visual Basic application okay so this is very uh simple tool but it has a lot of features you can send automated email you can send on behalf of someone or if multiple uh email ID has been registered in your outlook so you can select any of the email basis on on like ah the parameter provided in Visual Basic application okay so let's uh move to the Excel file and let me do one thing I'm just going to delete this uh even the all is okay and for for testing purpose let me send this email to everyone so whatever I mentioned the data labs to minimal so that it will go to my email box only so let's click on send and once you click on send it will take confirmation whether you want to send the email or not so just click on yes and as soon as you click on send it will start preparing on the email and sending it to all the users so you can look at this like here a lot of emails have been cons on uh composed by this tool and it has started sending so I think I okay let me okay so I think for testing purpose I have made this code to display only so while developing this tool we will later on we'll make uh it like we will provide two options to display or to send so once you are done with the testing purpose then you can uncomment so comment this display option and uncomment the send option so that when once you click on send button it will start sending the email without showing it okay so let me come back here and if you look at this so we do not need to send just to show that okay this has already drafted if you want to manually send just click on yes otherwise I'm just canceling it all this so this is very uh uh user-friendly application so let's start developing this uh you know uh this email automation from scratch so let's keep this file as it is open and to create uh no just press Ctrl n to create a new workbook and before creating this workbook you can you can create a folder anywhere let my let me create a folder in the same folder email Automation and here and that was the folder and why we are doing so just to keep this service list the Excel file and the data Labs image so that both the files which we need to embed this image we need to embed in mail body and this service dot list service list file that should be attachment so both the files will be available uh in the root folder of that particular application the Excel application which we are going to develop so that it will be easy for Visual Basic application to load uh both the files and use for attachment and embed okay so let me email automation version 1.0 okay and now we I need to copy this the service and the data labs to file that is the support file it's just paste over here okay and let's copy this the path where we need to save the new workbook so we have already created a new workbook so press Ctrl s to save this file and more options and browse and you can give the name email or to mentioned version 1.0 and this the extension should be macro enabled okay let's select this email version see so if you will not choose this macro enable extension then once you close this file then next time we will open the file Excel will not be able to return all those codes the Visual Basic code so in that way you all efforts will gone okay so now we are done with this let's uh quickly add one more worksheet that is for email list and home let's uh rename this home and the second one is let me come back to the demo file here and this one email list okay so let me rename this email list now we need to create this uh name a table name email email ID and Status so let's copy and paste the data from the same here this one press Ctrl C and move to this r file the current file and paste now you just want to remove the grid line just click on view and untick the grid line so that you can see only this table now we are done with the email list let's come to the home and in home we just need to insert some uh the emails and the header and the one that is the you user but sorry command button so let's do not invest much time in this let's copy this entire worksheet and while keeping your question this one A1 paste it now we can see that everything has been competent and go just go to the view tab here and untick this grid line okay so we are done with uh you know creating this home and email list press Ctrl s to save this file now let's come to this email automation the previous demo file press Ctrl W to close this I have already provided uh the link in description box so you can download the file the same file from the link available in description box and uh try to create the same application parallelly okay now uh to develop this application we just need to jump to Visual Basic application so just click on developer tab here and under code group just click on Visual Basic alternatively you can also press alt plus f11 to open the Visual Basic editor so let me click on this Visual Basic application here and as soon as your click on Visual Basic application it will open Microsoft Visual Basic for application and if you notice that this this is a separate application which is for development basically right so this is the Excel application and this is the Visual Basic application so in this project basically if you look at this we have only one project that is Microsoft Excel object and we have two worksheets sheet1 sheet2 the name is home and email list and we have one box one workbook to write the code we just need to insert a module blank module and a blank a module is nothing but a code container so that you can write a lot of codes over there some Precision function okay so let's uh click on insert and click on module now you can see that one module has been inserted that is the module one okay so now we have everything is ready like the support file uh the Excel file which we are going to attach the image file which we are going to use as you know in mail body of this uh email now we need this the email format basically this file so let's open this notepad and copy this sample email okay so starting from this to this and let me copy and paste over here okay so this format we will be utilizing to send this email okay so uh here after this providing we need to insert image and here we need to uh I provide the hyperlink and this username we need to make it uh Dynamic okay that will be coming from email list the second worksheet okay so let's start writing the code and then we will come back convert this all these text into HTML and we will utilize in male body the HTML mail body later on okay let's come to the Excel file here and start writing the code so first of all let's put the comment so that whenever uh any other user or the developer will refer your code he or she will identify the purpose of the code or the Visual Basic application code right so we are going to write a procedure to prepare and send the email so let's recommend Maybe procedure to prepare and send email okay and the name would be sub send email so in this sub procedure the sub and the enzyme in between we will start writing the code so first of all before writing the code we need to take uh two different parameters because we are going to make it Dynamic one is the email list and one is the username right uh sorry s underscore username as extensive Stream So as a string so that you can easily identify the type of this variable and the second one is as underscore email ID as string so again the both both the variables have string data type so username and email so while calling this function of procedure you can say we will be passing the username and the email ID so that how Visual Basic application can prepare the email and send the email basis on ah the input received from user okay so first of all we will declare your object variable to refer the Outlook application in mail item so declare variables to evidence of object types to refer out look application and mail item okay so to declare the variable uh the star it should be start with dim out app as object so this is the object variable Outlook application so it will refer the Outlook application now the second one is out Mill and object so this is output mail item okay and the third variable is the image the path of image so dim s i Am Her Image name add a string so we are done with declaring three variables let's start setting the reference so to put the command set the set the reference of Outlook application okay so to set the reference we need to use keyword that is set and out app is equal to so here uh we need to create object so create object and then they should be object type Outlook dot application now we are done with the creating the object for our click application next object that is required that is the out email that is the mail item basically so set out mail and so out afterwards create item because this is the mill item and it should be zero so now we are done with initializing both the variables out app and out ml Let's uh on error statements on error resume next so with the help of this statement we can resume to next statement okay and now uh let's start writing with out sorry out mail and end with now we need to write the code in between out and end with sorry with an end with block so as we are taking the user email ID in this and user email name in this variable so let's start so first of all dot two so this is the recipient email ID so let me copy this the email ID this this variable and paste over here so this will be dynamic it means we are taking the inputs uh from uh this variable right and suppose you have a multiple email ID uh no uh configured on your outlooks and you can send or click on sorry right from so as of now in my machine I have like in in Outlook I have only one email ID so I'm keeping it blank basically so if you have you just need to mention the email ID over there right now just CC so if you want to send the copy of this email so you can do that so I am putting an email ID so that is info info at the rate in the data Labs Dot org so I'm putting my email ID here so that in case if uh I will send this email to S email ID this one email ID and it will also copy to this one right now we need to attach uh the Excel file as well as the jpeg the image file so let's put attachments if attachments dot add and uh we need to take the attachments dynamically so both both the attachments here if you look at this now we have two Works two file service list and this both the files are available in the root folder so we can take the root folder with the help of this this work book Dot path and here so first of all we are going to attach this this Excel files Excel file is uh this one is service space list Dot Excel xlsx so this is done now the next attachment is uh the image so guess what here we just need to change the file name so the file name is the data Labs Dot jpg so I have attached both the file with the vlp Visual Basic application but if you look at this mail this mail only one the service list is showing as attached band and the second image is showing in as a embedded image in this male body and why we are doing so because in case if we will you know take the path of this this you know and we will use in the image source of mail body and once you will send the mailbox Mill and that will be delivered to user so on user system this app this path would not be available so it means the path or the image source is will be missing from that uh on that system and in that case the image will not reflect this image so that's why we are attaching these emails to this Outlook and then we will use as it you know Content ID basically so we will take the content ID and show in a male body right so in that way it will not show the missing so now we are done with this attachments Let's uh take this as image variable and this one is equal to so s image will be so I'm not going to change the name image name so I will keep this name this one so either you can take this the content ID basically or you can take the name so in here I am keeping the data labs.jp is the same name which is available as a source so I will keep the same name to refer that particular content and show in mail body right so I'm assigning this name to S image now I need to create uh like I need to get the subject so subject here and the subject would be so you can put any subject so let me come back to this and I will take the subject line from here the male body so now we are done with the subject now the main thing is to write this male body here so that it will show this information dear Robin and everything so this is the middle body having image and everything so let me start writing the code here that is dot HTML body equal to and then now we need to pass this HTML code here so let's move to the X notepad file and let me show you so this is the notepad file right so we need to convert this notepad file sorry the simple text into HTML so let's put here in starting that would be uh HTML and then we have body yeah and then let's come back to the end just copy this and paste over here and end the tag okay so we are done with HTML body and HTML body now we need to start with paragraph Superior paragraph tag is simple P here so p and then we we need to make it a dynamic this one so we will put this this and ampersand let's come here as usual name so let's copy this as username that we are getting from this uh while in s username variable so let's copy here and so this would be dynamic here and again ampersand okay now we need to break this line because here if you look at this so we need to to use BR simple BR again one more time VR so it will one time it will break the line and the second it will keep one line you know blank so to break line okay and again after this thank you for inquiring this statement we need to provide again to break line so uh sorry line break basically so BF just copy and paste over here so we are done now again after this in meantime all this we need to put this brake line over here again after this brake line again after this regards this one this one so we are done with the brake line Sorry here we do not need to show the blank line so we will keep one beard for the line break right okay so we are done with this now we need to uh you know provide the hyperlink over here the detailabs.org so that if user will click on this www.datalabs.org it will jump to uh a jump to the browser and open this data labs.org website so now here we need to put a sorry like hyper reference href and hrf is equal to www dot data labs.org and after this we need to close this tag this a okay so we are done with this all this code now we need to attach the email so to attach the email uh let's start with and that would be our image and SRC source Azure Source I have already said that like we will be using as a Content ID and in content ID we will providing the image you know name okay so Source would be uh CID and then Content ID and then this one and ampersion and let's come here let's take this variable name as image and put here again ampersand okay now we need to provide the hyper reference over here so let me copy this hyper reference code here's this this one and we will hyper reference the same same path that is that the detailabs.org and let's copy this this one this way and let's create two brick line break here so b r and layer so we are done with all this now we need to put this the end uh paragraph This closing this P1 okay and in Visual Basic application code we need to put all these in one line so let's uh let's make it all the statement in one line basically so here remove this again and because we have already provided this BR and BR so it will convert it so no need to worry about that now as we are going to pass this whenever we are using double quotes we need to uh make it again like again provide the double quotes here and again the same would be a this one let's copy this entire statement come with come to this uh the send email sub procedure and paste over here so now we are done with this HTML statement the HTML body if you look at this there is no error right so it means we are pretty much good we will see while testing this application so no need to worry now we like we are done with sending the email and everything so like preparing the email of the mail body so we need to or display okay so display if you want to see the image email before sending it you can use this dot display and if you want to send it directly basically so you can use dot send so for timing I am keeping this dots and as a comment and I will be using this display so that if I will be uh no 100 sure that okay this or the statement the Visual Basic statement is drafting email properly and correctly then I will make it a comment and remove the comment from send okay now we are done with this preparing and displaying the mail now we need to set this reference to nothing so we need to release the memory of which has been assigned to Outlook application in Outlook email so let's copy this set out and that will be nothing mistake yeah let's copy again this one so it should be previous ones first we will release this memory of this one and then application which is equal to nothing so we are done with this code let's come to the debug compile and here it's showing this create object so spelling mistake c-r-e-a create object let's come debug so no error as of now let's create some room so now uh we are done with writing the code to uh you know create the email uh we need to write one sub procedure so that we can send the email to everyone and pass this uh s username and S email ID while calling this send emails so let's come here down down and start writing the code so put the comment VBA sub procedure to start email okay and let's sub let's start underscore email let me create some room so that you can see clearly now we need to declare your variable uh to take the confirmation from user so name I confirmation as VB message box results and we will store this confirmation VV message sorry message box and do you want to send the emails right so we are asking a question and we will we be questions plus VV yes no so here here we are asking a question to take the confirmation so the icon type should be VB question and the option we will be providing yes no so that user can click on yes or no okay and then here uh the title should be confirmation so say suppose like user is uh clicking on no basically so if I confirmation formation is equal to VV no then what we need to do we need to come out from this start email so that would be exit sub okay so if user will select yes then we can proceed with the code so now we need to declare a variable that would be dim as such as worksheet and then we need to decrease the variable on dim I row and integer okay let's set the variable uh reference of such worksheet so set sh is equal to this workbook Dot shoots and we will refer this email list so email list now now we need to initialize this I row with two because if you look at this in this email our email ID starts from Row 2 okay so we will initialize the variable with row or I row that is 2 and we will keep increasing by one so that it can check whether information is blank or not or if the status is done or blank and then if you keep sending the email to all the users so let's come to this Visual Basic application and I rho is equal to 2. okay now let's start writing the looping code basically a looping structure here so I am going to utilize do while so do while and then sh sh VR referring this email list to hsh Dot range and if Ampersand I rho dot value is equal to not blank and then Loop so it will keep you know checking whether I rho A 1 A2 sorry A2 A3 A4 A5 and it will running until it will not find the value as blank basically right so let's here I rho is equal to I rho plus 1 so we will increase the value of I rho by 1. right now uh we need to check uh the column C basically if the value of column C is blank or not so if the value of C is blank it means we have to send the email and if the value of C is not blank it means we need to skip that particular email ID okay so let's come to this Excel application here and now let's give sh Dot range C ampersion I row dot value is equal to blank and then and leave okay now we need to call this function this function name so let's come to the top here and copy this come to this in between if and and if and here put call okay and let me delete so as such username so that is first of all we need to pass this username here so username is available in so let me copy this value and the username is available in column A and the email ID is available in column B so let me come to the Excel file so that you can see so column A has user and column B has email ID okay so let's IO and this is b i row and once we will send this email then what we need to do we need to update this the column C basically that should be equal to equal to done now Ctrl s so we are done with this start email let's debug compile and you can see no error is showing as of now so let's uh jump to the Excel application the workbook file and assign this uh and then we will call this uh start underscore email on the button the command button over here right so let's click on here come to the home and we need to assign the macro on this send email button right click on that and assign my group and here assigned macro just uh this open workbook this workbook and this one starting and click on OK Ctrl s to save this file or now we are done with this so let's start testing uh in email list the status column should be blank if there would be any value it will not send the email for that respective record okay so let's come to the home click on send button click on yes and now you can see that it is started drafting the email So currently we are showing the feature to display uh the email so let me uh include this all these emails which have been drafted by this application itself okay and if you want to change this like if you can see all these book codes are working perfectly let's go to the developer and Visual Basic and here uh here this way like here we just need to comment this the display and uncomment this send button and press Ctrl s to save this file and let's close this Visual Basic application code and come to this email list let me open this remove the two only so that it can send email to Alam and Robin let's come to the home and click on send button click on yes and if you come back to the this one send item here look at this two different emails have been sent just few seconds before that is one is to Robin having all the contents which we have already uh drafted with the help of Visual Basic application and the second one is Muhammad Alam that is sorry the detailabs.org this one okay so now you can see that our code is working perfectly and it's very easy to create this thanks for watching please like share comment and subscribe have a great day bye

Channel: TheDataLabs

Views: 19,312

Keywords: VBA to Send Email from Excel with Attachment in Mail Body,  Show image in mail body without losing the reference, Attach an Excel file to the email, HTML to create the mail body, Outlook Automation in Excel, Send Automated Email from Excel

Id: SrmOKOneVAo

Length: 38min 46sec (2326 seconds)

Published: Sun Sep 18 2022