Send serial email with Access, VBA and Outlook

I am showing how to write #VBA code in Microsoft #Access to send a serial email with Microsoft #Outlook.
You can read the full article here:
codekabinett.c...

[@miklosracz0220]

Dear Sir, Thank you very much this lesson. I have seen a bunch similars, but this is the greatest. Especially because you insert a loop which go through the recordset.

[@javedahmed8343]

Sir, I saw so many videos but this your video fetched me the desired output. Thanks a lot. May God bless you

[@walternascimento1255]

Congratulations! There were several searches and the only tutorial that really worked! Thanks

[@tommyd1376]

This is exactly what I was looking for. Helped me out a lot. The only change I made was added a message box to verify email sent. Just a visual thing for me.

[@stephenheusz3236]

I wish I had found this months ago. I created a similar application but really struggled to understand what I was doing. Your explanations are very clear and now i feel as if I have actually learnt something, rather than just achieving something.

[@queenlioness7056]

Thank you for this video. I will try it on my Access Database.

[@maneasc8182]

This is a great tutorial. Your explanations of the code structure was fantastic - Thank-you!

[@sergeykozlov6023]

Great! It works. Besides I learnt some tricks on Access from Guru in his careful explanation of the lesson. Thank you very much!

[@ElderX2]

Fantastic videos I am learning lot's of new things. Thank you.

[@johnclark851]

Excellent video. I really like your technique on checking to see if other apps are open. I will be using that in all my apps as well. Thanks for posting!

[@KeffelewAssefa]

Thank you, great tutorial.

[@TonyTony-dn2ir]

Beautifully explained. Liked, subscribed and notified.

[@crafty_tams]

Thank you so much for this fantastic video!

[@GamersRebirthDL]

You are the best! Thanks Bro! This video is Amazing and straightforward. Keep it up man and good luck! If u need any gaming emulator rom links lol let me know. 👍🤘😉

[@gdcdatabaseservices]

Thank you very much, this was excellent. Question, I have an MS Access Database for my customers that run in MS Access Runtime, and I want them to be able to send email just as you showed. I do not want the clients they are sending emails to, to reply back (block it) and I want it to show the email is coming from an alias from my main account. Saying that, is this the setup for sending emails I need?

[@johnwet5798]

This is what the thing I want. Thanks

[@andrewabaye6603]

Beautiful Video and it pointed me to exactly what I was looking for. If I want to attach report as PDF, what will my code be please?

[@trooperstravels]

Great work very easy to follow. If you have multiple email accounts in outlook how do you select a specific from email account? I've tried several different ways but can't seem to get it to work. I have to create the emails and not send immediately, manually change the from email address and send.

[@szilviatoth3962]

This is a really helpful video to vba beginners like me, it works! If I need to send a table or query result as xls attachment to outlook, what exactly should I add to the code and where? Thank you.

[@codekabinettcomen]

If you want to send the same file to everybody its fairly easy. You first need to export the data to xls. To do that add these lines before you open the recordset with the email recipients. Dim fileName As String fileName = "c:\tmp\exportfile.xlsx" DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "yourTableOrQueryName", fileName Then you need to add the attachment to every outgoing email. To do that add this line somewhere between Set outMail = ... and outMail.Send. outMail.Attachments.Add fileName Done. It's more complicated to send a custom export file for each recipient. You would need to export the data inside the loop then and probably need to add criteria to the query to export only the records for that recipient. - That is beyond what can be explained in a comment. I'll probably make another video about this within the next couple of weeks.

[@codekabinettcomen]

Changing the procedure from Sub to function does not affect what it does. The RunCode-Macro is a valid reason for doing so. I would recommend you double check your query with the email recipients. Does it contain you address multiple times?

[@szilviatoth3962]

I did not pull email address from a table like you, I was testing it only with my email address that I typed manually in the code. I found out that with removing the loop section, solves my problem. It was sent 3 times to my outlook because I have 3 records in my test table. Now everything is working perfectly even with runcode macro. Thanks for the help!!

[@davidgray9242]

How can you define the email account from which the mail will be sent - does it always have to be only the default outlook account?

[@codekabinettcomen]

You can use the SendUsingAccount property of the MailItem to define the account it is sent from. Here is an example: codekabinett.com/page.php?Theme=11&Lang=2#send-email-outlook-vba-sender-account

[@aistedulkyte2791]

Hi, I am trying your code and my code is not picking up data from Database. I checked a hundred times, database name is correct. Cannot understand what is wrong? Any chances that you can help? :)

[@codekabinettcomen]

If there is no error but you are not getting the data you expect, there most likely is a problem with the query criteria you use. - Try to build your query in the visual query designer step by step, checking results after each. Finally, you can switch to SQL view in the query designer and copy the SQL over to VBA.

[@chriscline9381]

How would you expand this to add an attachment?

[@karagidesa]

Hello again, I am trying the command mailitem.attachments.add on the code, however it doesn’t work. Would it be possible to crarify the path? For example is it mailitem.Attachments.Add (“C:\test\broshure.pdf”), (.pdf) . Thank you very much in advance.

[@codekabinettcomen]

Just use: mailitem.Attachments.Add "C:\test\broshure.pdf" The type argument is optional and not relevant in 99.9% of cases.

[@adrianapenaloza9619]

hi! this is great. :) is there a way to send emails from a shared inbox? like on behalf of another account? thanks so much!

[@codekabinettcomen]

Yes, if the other account is also configured in Outlook, you can send email from that account. Here is a explanation how to do it: codekabinett.com/page.php?Theme=11&Lang=2#send-email-outlook-vba-sender-account

[@scottnorman2296]

Great video, Thank you! Why do emails appear in outbox instead of sent mail?

[@codekabinettcomen]

Because technically they are not sent yet. The get queued in the outbox for sending. When they are actually send depends on your Outlook configuration.

[@kiwikiki718]

Video is great. How do you add an attachment to the email if you have a field that includes the path?

[@codekabinettcomen]

Thank you! To add an attachment is pretty simple. With outMail being an Outllok.MailItem, you add this line to the code to add an attachment from a path store in the recordset. outMail.Attachments.Add rs.Fields("FieldName_PathToAttachment").Value

[@Jihaoui]

very good , thank you

[@stanTrX]

thanks, works smoothly.. do you think we should better use "clone" of the recordset, instead of the original? is there any difference on that?

[@codekabinettcomen]

If you created the recordset explicitly for this purpose there is no point in using a clone of that recordset. A recordset clone is useful if you need multiple cursors on the same set of records. E.g. you use the recordset of a form for another purpose.

[@stanTrX]

codekabinett.com/en many thanks

[@stanTrX]

@@codekabinettcomen is it possible to trigger this module to a button in a form?

[@codekabinettcomen]

Yes, sure. I would create an event procedure for the button and just call the routine to send emails from there.

[@libingeorge2104]

Hello Sir, When i am running the coding/ trying to send mail. there is one warning message coming before sending the mail. it is asking deny or allow message. to avoid this warning message what we can do.. ? please leave your valuable answer.

[@chadighafari776]

Brilliant work! Thank you! Can I send the emails even if I have some addresses missing? ie 100 customers with 10 customers that don't have an email... Access bugs at the first "blank" fiel

[@codekabinettcomen]

Just filter out customers without an email address in the query to get the recipients.

[@birdgreen2256]

Need your support to enclose a report attachment in your same code since I tried and I’ve got 2 separate outcomes sent email and the other is a display of the attachment .. please provide me the additional code if possible.

[@codekabinettcomen]

Not sure what you are asking. Look at the .Send and .Display methods of the MailItem for sending or just displaying the email. Use .Save to save the message as draft.

[@birdgreen2256]

@@codekabinettcomen Thanks a lot, just I want to adjust the code in order to add attachments.

[@garycurtis]

Excellent Video, is there a way to have it send from an alias email address?

[@codekabinettcomen]

Maybe this link can help: codekabinett.com/page.php?Theme=11&Lang=2#send-email-outlook-vba-sender-account

[@ivanvilches2890]

First, thank you for the video and instructions, really easy to follow. Got it working perfectly! I am struggling to add a signature that has an image in it. I tried changing to HTML or calling the .htm signature from outlook… but I don’t get to work. Is there an easy way to add a signature with the image in this code? Thanks and advance and keep up the good work.

[@codekabinettcomen]

To get an HTML email you need to prepare your HTML text and then set the HTMLBody property of the MailItem with your HTML text. Using the predefined signatures from Outlook is a bit of a pain. I would ignore them and also create the signature with your VBA code. I've got an older text on creating HTML emails with embedded images here: codekabinett.com/rdumps.php?Lang=2&targetDoc=howto-create-html-email-access-vba-outlook

[@ivanvilches2890]

@@codekabinettcomen Thank you for the quick response! I have been able to play a bit with changing the format to HTML and got it working, I will look at the info provided and will let you know how it turned out. Thank you and once again!

[@lwazimace2508]

Genius.

[@adityabajaj6352]

Brilliant and simple, can you please advise how to add a table in the email text?

[@codekabinettcomen]

Thank you. To create a table in HTML you need to build the full table structure in the string. Most simple example: someValue - Look up these tags in an HTML reference.

[@mistyheyden]

How would we do this and send one email to multiple addresses?

[@codekabinettcomen]

You can add multiple email addresses separated by commas to the To-property of the Mailitem. If you need to retrieve all those addresses from multiple records in a table you can take a look at this article for a possible solution: codekabinett.com/rdumps.php?Lang=2&targetDoc=access-vba-output-column-values-comma-separated

[@stanTrX]

is it possible to change the code to "html body format"?

[@codekabinettcomen]

Yes, it is. Check out the BodyFormat and HTMLBody properties of the MailItem. I wrote a text on creating HTML email with VBA, which might be a helpful reference. codekabinett.com/rdumps.php?Lang=2&targetDoc=howto-create-html-email-access-vba-outlook

[@ericclapton2150]

I tried this on Office 365 with interesting results. when i run the code, it makes one pass through and sends the email perfectly. however, when it tries to repeat as part of the loop, when it gets to the "outmail.send' is throws an error "Outlook does recognize one or more names" and it highlights the "outmail.send". then quits Any Ideas?

[@codekabinettcomen]

Any chance this only happens with an email recipient that has a comma in its name? There is setting in Outlook if the comma should be treated as separator in the recipient list of an email. This feature sometimes behaves inconsistently; e.g. for an completely unknown recipient it works but once Outlook knows the recipient it will reject the part delimited by the comma. - Try turning off that setting in Outlook.

[@codekabinettcomen]

In any case, you can use the Recipients property of the Outlook MailItem to identify all recipients Outlook derived from the addresses of the email. There will be one that Outlook cannot resolve. If you know which it is it will probably reveal the root cause of the problem.

[@ericclapton2150]

@@codekabinettcomen No, that wasn't it. see my next comment

[@ericclapton2150]

@@codekabinettcomen Yes. this was the problem. a legit gmail address, without spaces or anything was the problem. to test, i used the same email for 4 names and the system sent out 4 emails. Now, can you help a newb one more time with the code i need to test recipient. i assume the results would be like an RS in that there would be a list of emails that were sent, is there also a list of failed? i looked here:docs.microsoft.com/en-us/office/vba/api/outlook.mailitem.recipients but i don't know how to invoke the code for what i want. thanks again for the video and the help.

[@codekabinettcomen]

Next comment? -[Edit] Found it. Was held for spam review. - Approved it.

[@kabal5520]

Hi :) I have a question. I have to send information about certain amount of customers (name etc) to one recipent. Is it possible to send it in one message, instead of many?

[@codekabinettcomen]

You need to add all the information for the email in a long String variable and then used that for the email text (Body).

[@RAVIKUMAR-oc2lm]

Sir..the video is really great... But I have one doubt.. I have report which is same for each user(but updates along with the query) ...and I want to send it in PDF format for each of them..how can I achieve this?

[@codekabinettcomen]

Good question. I'll probably address this in more detail in the future. Where are you struggling? I made a video on creating PDFs from reports. kzbin.info/www/bejne/Y6aqhYRmo7ijrLc That should cover part of your question. If you are using Outlook, you would need to add the PDF generated for each user with the Attachments.Add method of the MailItem. HTH

[@MmM-yo4kz]

Hi, thank you very much for your excellent code - it really works. However, when I change my query to updatable one which depends on a form (the query's criteria like ([Forms]![Users]![User_ID]) I get "Run-Time error '3061' Too Few parameters. expected 1". Could you please help me with it?

[@codekabinettcomen]

The "too few parameters" error is usually caused by a mistyped column name or a similar error in the reference to your form control.

[@MmM-yo4kz]

@@codekabinettcomen Thank you for quick response. This error appears only if I change any criteria of my query to updatable (as I said like ([Forms]![Users]![User_ID]). If the criteria is permanent (for instance, UserName - "Smith") it works. I don't change the code, I only change query parameters. And debug indicates when I hover "rs.Fields("FirstName").Value=.

[@codekabinettcomen]

Then there is something wrong with the reference to the form control.

[@MmM-yo4kz]

@@codekabinettcomen But the reference works when I run my query using the form. Could you try to make the query, which your code refers, updatable-depended on a form, and try to launch your code? Please help me, I am really struggling with this problem.

[@codekabinettcomen]

Sorry, I think I missed part of the problem. When opening a recordset direct form control references cannot be resolved. You need to concatenate the SQL string with the value of the form control in VBA. This should be explained in this video: kzbin.info/www/bejne/mWPCl3iae6edlbM

[@karagidesa]

Great Video, very helpful! Please advice how could I include an image in the body of the text, and how could I attach a pdf file. Thank you very much indeed!

[@codekabinettcomen]

Thank you for your feedback! Right now I've only a text explaining that: codekabinett.com/rdumps.php?Lang=2&targetDoc=howto-create-html-email-access-vba-outlook Your question was asked quite a lot. So, I think, I will also record a video on that topic. It will take a couple of weeks though, before that comes along.

[@karagidesa]

@@codekabinettcomen Thank you very much, indeed!

[@birdgreen2256]

when i run i do get >> Run-Time error '3061' Too Few parameters. expected 1

[@codekabinettcomen]

Most likely there is a typing error in one of the field names of our SQL query.

[@birdgreen2256]

@@codekabinettcomen Yes you are right and I have solved it, really you are great .. thank you so much for your great support.

[@birdgreen2256]

Need another help .. I have a flied sentemail (Yes/No) And I want to change the value to true when we sent the email so that I don’t repeat sending same email to the same person again

[@codekabinettcomen]

Just add this to the email send code and set the field to true. If your query is not updateable you need to execute an update query on the field.

[@minnal383]

Will this work with Access 2003?

[@codekabinettcomen]

I can't say for sure out of my head, but it very likely should do.

[@rajneeshrana4282]

sir if i need an attachment in this. where i want to include data from a query.

[@codekabinettcomen]

You need to export the query to an HTML file and attach the file to the email. If the data is different for each email you are going to need a temporary query for the export that is adjusted for each record. That query can be used in docmd.TransferText acExportHTML to export the HTML.

[@BenjaminFernandini12449]

Great

[@jassimal-mansor8231]

thanx a lot . I want to see the email before send it , I try to use docmd.openreport >>>> but it send to only one email not all

[@christinachahin4559]

Hi codekabinett.com/en, Your video is great! How could I modify the code so that if my table has multiple lines for one user, it could group them and send only one email to the person as opposed to an email per line? Thanks,

[@codekabinettcomen]

You can sort the query by the email address and store the current email address in a VBA variable. You then concatenate all the text for the email from the recordset in a another variable and finally create and send the email only if a new email address is in the next record of the recordset.