VBA Unleashed: Send Customized Emails in One Line

Рет қаралды 10,614

Күн бұрын

👉 Ready to master VBA?
- Check out my full courses: courses.excelmacromastery.com/
- Subscribe to the channel here: bit.ly/36hpTCY
-Want to download the source code for this video? Go here: rb.gy/xkv38
(Note: If the download page doesn't work then make sure to turn off any popup blockers)
VBA Unleashed: Send Customized Emails in One Line
In this video, we're going to show you how to send fully customizable emails from VBA (Visual Basic for Applications) with just one line of code.
If you're tired of spending hours configuring VBA email code for each scenario, then this tutorial is for you.
First I cover how to create and configure emails in Excel VBA and then I show you how to do it all in one simple line of code.
#ExcelVBA #VBASendEmail #VBAOutlook
Useful VBA Shortcut Keys
========================
Debugging:
Compile the code: Alt + D + L OR Alt + D + Enter
Run the code from the current sub: F5
Step into the code line by line: F8
Add a breakpoint to pause the code: F9(or click left margin)
Windows:
View the Immediate Window: Ctrl + G
View the Watch Window: Alt + V + H
View the Properties Window: F4
Switch between Excel and the VBA Editor: Alt + F11
View the Project Explorer Window: Ctrl + R
Writing Code:
Search keyword under cursor: Ctrl + F3
Search the word last searched for: F3
Auto complete word: Ctrl + Space
Get the definition of the item under the cursor: Shift + F2
Go to the last cursor position: Ctrl + Shift + F2
Get the current region on a worksheet: Ctrl + Shift + 8(or Ctrl + *)
To move lines of code to the right(Indent): Tab
To move lines of code to the left(Outdent): Shift + Tab
Delete a Line: Ctrl + Y(note: this clears the clipboard)

Пікірлер: 43

@Excelmacromastery Жыл бұрын

Let me know in the comments if you find the one line method useful for sending emails😀

@debbie6085 Жыл бұрын

Thank you for the great video. I love code that handles multiple options 😀

@Excelmacromastery Жыл бұрын

Glad you like it.

@makubexho Жыл бұрын

Great video as always. Thank you Paul

@anthonygeo3 Жыл бұрын

Great video as per! Super clear and logical steps to guide you from one end to the other. Thanks

@Excelmacromastery Жыл бұрын

Thanks. Glad you liked it.

@free3690 Жыл бұрын

Another amazing video. I send many emails and this function can come in very handy. Thank you for putting it together!

@Excelmacromastery Жыл бұрын

You're welcome.

@KM-co5mx Жыл бұрын

This is a great one! 🤩

@Excelmacromastery Жыл бұрын

Glad you think so!

@user-vb1rn6ox8n 11 ай бұрын

Great video!!! It’s exactly what I was looking for❤

@MrAlmolo 9 ай бұрын

This is great! Thank you!

@sasifexcelsolutions4509 10 ай бұрын

Thank you, off the topic, I watch almost all your videos, I'm looking for advanced filter where the source data is outside of the active workbook. Like code to open the workbook, run the advanced filter and the close the workbook. Thank you for you videos.

@josecarlosconejo5724 Жыл бұрын

Very useful well explained. You got a new subscription

@pbs36 Жыл бұрын

Very nice video and code. Will likely be using the code in the helper module in the future. With some error handling when adding the attachments, that seems to be the most likely point of failure, it seems to be very close to ready to use. I will probably integrate also your solution to add charts without using the Clipboard, and make sure to credit you on all the code!

@Excelmacromastery Жыл бұрын

Thanks. Glad you're finding use for the code

@budiyanto1531 Жыл бұрын

Excellent. Another great lesson. Thank you Paul ❤❤❤. Request for the next lesson “Sending Whatsapp masssages using VBA”. 😁

@Excelmacromastery Жыл бұрын

Glad you liked it. I'll keep your suggestion in mind.

@b99eu Жыл бұрын

And a video to send a MS Teams Chat message to a specific person. Including to choose the correct teams account… 😊

@abdulrehmanmughal8235 Жыл бұрын

hi sir hope your doing well, Thanks for your contribution in excel vba learing, I have a question when the email send by vba how can i check if the email is not delivered or not without opening outlook by code at the specified email address.

@rods6405 Жыл бұрын

Been doing this since early 90's for sending quotations proposals and product info etc No more fax machines secretaries etc could prepare and send a quote in 30 seconds.

@Excelmacromastery Жыл бұрын

It was a real game changer back then.

@MegaJohny777 Жыл бұрын

Do you have a solution for using an other "from email" ? I don't think that works good anymore with Office365 and outlook.

@houstonvanhoy7767 Жыл бұрын

Paul: At 00:47, you add a reference to the Microsoft Outlook 16.0 library. What is the scope of that reference? Just to be clear, is it in force (in memory) just for this VBA module, or is it part of my Excel VBA set-up until I decide to remove it? Also, can other people use this code if I share the specific Excel file? Thanks.

@Excelmacromastery Жыл бұрын

The scope of that library is for the current project(i.e. workbook) only. You need to add it for each one which makes sense as you don't want to have a library referenced that you are not using. In theory the file should work but it tends to causes issues every now and then so that's why we use Late binding when distributing the workbook. Late Binding doesn't use a library reference and instead used CreateObject to connect to the library when the code is running(see this video kzfaq.info/get/bejne/jZZ2n6581cC8l6s.html for more)

@7Denial7 Жыл бұрын

Thanks for your videos, Paul! 😊 Can I ask you a question? Is your main full time job VBA coding? If so, what kind of job is it? I was wondering If one can still get a job as a VBA developer

@Excelmacromastery Жыл бұрын

The easiest thing to do is check job listings to see what is available. I would say that there is a lot less pure VBA developer jobs than before meaning that they are usually mixed with other skills.

@houstonvanhoy7767 Жыл бұрын

@Denial7 A friend of mine in my age group works full-time as a COBOL programmer. That is older than VBA.

@Excelmacromastery Жыл бұрын

@@houstonvanhoy7767 There is a big demand for Cobol programmers because there is a lot of legacy mainframe systems in big organizations still running on COBOL.

@ivanxdxd Жыл бұрын

Hi. Quick question. How could one use a better code editor for vba? Is it possible to use Vscode somehow?

@Excelmacromastery Жыл бұрын

Look up TwinBasic.

@AlihanOZan 10 ай бұрын

Hello, I can't add outlook signature to my excel VBA Could you provide me a solution about it? Thanks

@Drzeny Жыл бұрын

Using the same approach as you have in this video, are you aware of a good way to pass through a graph object to the send email function so that the graph is included in the body? I cannot find any good support for it so end up copy/pasting the graph which then ties up the clipboard (which I want to use for hyperlinks since passing through an array destroys them..) Great video!

@Excelmacromastery Жыл бұрын

You can do it like this: ' Get the path and filename for the graph image Dim chartPath As String, graphHTML As String chartPath = ThisWorkbook.Path & Application.PathSeparator & "mygraph.png" ' Export the chart to the filename Dim myChart As ChartObject Set myChart = shEmails.ChartObjects(1) myChart.Chart.Export chartPath ' CREATE EMAIL HERE ' add file as hidden attachment .Attachments.Add chartPath, , 0 ' Create html to add file to the email graphHTML = " " .htmlBody = graphHTML ' SEND EMAIL HERE ' Delete the file Kill chartPath

@Drzeny Жыл бұрын

@@Excelmacromastery Ah ok, so I need to save the graph as a file and add it as an attachment? VBA does not have a very good integration with outlook I think. Thank you for your reply!

@hammeedabdo.82 10 ай бұрын

Have you already explained all the principles of object-oriented programming, such as inheritance, encapsulation, abstraction, and polymorphism, through building applications to understand these concepts?

@raybooth3001 4 ай бұрын

Doesn't work with New Outlook!

@twiddle7125 Жыл бұрын

All I had to do was move the .Display and use the .HTMLBody?? I researched forever to try to get my signature to be included.... I am experiencing a weird bug, though. I am adding an html span tag to specify my typeface and size since it won't use Outlook's default for some reason, but I can't set the size to 12....I tried 16px and 12pt, but those come out as 10. All other values of px and pt correctly translate as expected....I'm losing my mind with this bug, lol. 11.5pt yields 11.5, 13pt yield 13, but 12pt yields 10. 18px yield 13.5, 14px yields 10.5, but 16px yields 10....whyyyyy? lol

@BenLinfordUK Жыл бұрын

This is quite common phenomena... I think the best thing to do would be to get the HTMLBody output and look to replace the exact section of code in the pre-existing CSS Style that gets bundled within the style of the email by default. I found a nifty little subroutine somewhere which uses FSO (FileSystemObject) and TextStream, which can write out to a .txt (Text File). You could then drop that text output into a code editor (so that you get the syntax highlighting to make the output easier to navigate through). Then you can identify which substring you need to target with the Replace() function in VBA for example to adjust to your desired font-size. Hope this helps. 🤞

@ousmanetall1286 11 ай бұрын

Hi you're very professional, but your speed of teaching is too fast, and explanations are not enough. Please, I want to learn more from you and want you to be as understandable as possible. From Senegal, a country of africa

@hammeedabdo.82 11 ай бұрын

New.....Now Python in Excel...Bye Bye VBA!

@Excelmacromastery 11 ай бұрын

Python is not replacing VBA as the scripting language for Excel. It will simply be available in cells to manipulate data.