Send multiple emails to multiple recipients

[YasserKhalil]

Hello everyone
I have a code that send one message to multiple recipients

Please Login or Register  to view this content.

But I need to send multiple emails as attached and if possible make attachments optional by using check boxes for example

Thanks advanced for help

[Kenneth Hobson]

The code that you posted simply sends emails per row. I would not say that it sends to multiple recipients unless preset in column A cells. It does not match your data as it seems to start at row 6 and your loop starts at row 3.

You have merged cells so B2 and C2 will have no data for Subject and Body.

As for the checkboxes you have none and if you did, what are the attachments? Do the files exist already, or generated and if so how?

For just the sending email by row, I could modify your code but I like to use early binding for newer users to access intellisense.

Please Login or Register  to view this content.

[YasserKhalil]

Thanks a lot Mr. Kenneth ...
I had to arrange my ideas before posting. sorry for that
Can check boxes added automatically in a separate code as I have a long list and it would be exhausting to add a check box for all?
Here's a sample .. attachments are unknown in number may be one or more and may be none

[YasserKhalil]

Any help in this topic please?

[YasserKhalil]

I am still waiting Mr. kenneth
Can you help me please?

[LJMetzger]

Hi Yasser,

See the attached zip file that contains a modified copy of your Excel File, and contains copies of your 3 sample data files in case anyone else wants to try to use the modified Excel file.

The Excel file contains code to:
a. Create/Delete CheckBoxes.
b. Send Emails (using Outlook) to the User(s) on each data line with the appropriate attachments.

To send one Email to multiple users, separate each Email address in Column 'A' with a semicolon (.

The code does not close Outlook because most users keep Outlook open all the time.

Please note that 'Late Binding' is used which avoids the necessity to have a VBA library reference to Outlook. For more information see:
https://msdn.microsoft.com/en-us/library/0tcf61s1.aspx
http://peltiertech.com/Excel/EarlyLateBinding.html

Some newer versions of Outlook have NASTY Security messages when using VBA to access Outlook. The messages can be suppressed if your Anti-Virus software is up to date and you follow the following instructions:
To prevent OutLook Security Message such as (and to remain secure):
a. A program is trying to send an e-mail message on your behalf, or
b. A program is trying to access e-mail addresses:
Make sure Anti-Virus software is up to date
Go to Windows 'Start' Menu
Right Click on 'Outlook 2016' and select 'Run as Administrator'
File > Options > Trust Center > Trust Center Settings > Programmatic Access
AntiVirus status : Valid' should be displayed in the middle of the Screen.

The Email code in Ordinary Code module Mod ModOutlookEmail follows:

Please Login or Register  to view this content.

Lewis

The remainder of the code and code for CheckBoxes is in the next post due to size restrictions.

[LJMetzger]

Previous post continued:

Remainder of the code in Module ModOutlookEmail:

Please Login or Register  to view this content.

CheckBox code in Ordinary Code Module ModFormsCheckBoxes:

Please Login or Register  to view this content.

[YasserKhalil]

That's amazing and fascinating Mr. Lewis
I liked it a lot
You are wonderful. Thanks a lot for this masterpiece

[YasserKhalil]

Hello again Mr. Lewis
Sorry for disturbing you in this topic again
I need any one of these options to work (don't need all of them, any one will do):

* Ability to add an outlook signature.
* Ability to format text like color, weight and size.
* Ability to put html in the body field.

[LJMetzger]

To add an Outlook signature see the code associated with Post #6 in the following thread: http://www.excelforum.com/showthread...t=#post4570827

[LJMetzger]

The following code uses HTML tags to format text in the Outlook body. The code has references to a couple of links to HTML references. In the code you have to use

Please Login or Register  to view this content.

Try the following sample Macro. To simplify the code, Outlook MUST be open before running this Macro. If Outlook is NOT open, and the code fails, you may have to Close and then Reopen Excel to get the code to work.

Please Login or Register  to view this content.

Lewis

[Kenneth Hobson]

The .htmlBody method to get a signature after a .Display as Lewis showed in that link is an easy way to do the signature. Of course standard html coding as Lewis also demonstrated is an easy way to make html code if you know the syntax.

Other ways to add a signature and a routine to convert a range to html (RangeToHTML) is shown at Ron's site.
'More Excel to Outlook Examples: http://www.rondebruin.nl/win/s1/outlook/bmail4.htm
'http://www.rondebruin.nl/win/s1/outlook/signature.htm

[YasserKhalil]

Thanks a lot for very helpful replies.
I tried to add signature as illustrated in post 10 and it is ok .. But after formatting the signature in Outlook, I found that it is sent plain with no formatting
Tried to change this line

Please Login or Register  to view this content.

to this line

Please Login or Register  to view this content.

But doesn't work .. and I got the message scrambled in signature
Any idea how to keep the format of signature?

[YasserKhalil]

After some tries I found that the code in Post 6 in the link attached have the solution as I have to display first

Please Login or Register  to view this content.

Then within With statement to display again (I don't know if this is relevant or not) then assign

Please Login or Register  to view this content.

then finally .Send

Or it is enough to change .body to .HTMLBody

Sorry for the confusion
Regards

[YasserKhalil]

@Mr. Lewis
Please be patient with me. I am just a beginner in the aspect of Outlook
As for your awesome file in post #6 .. How can I add signature parts? as I am lost in the lines of code
Please don't attach a new file as I need to learn. Just point me the name of the procedure and the line at which I need to add or edit to be able to follow changes
Thanks advanced for help

[Kenneth Hobson]

I don't see why that would not work. Of course using that method, you must have a signature set to display for new emails.

Please Login or Register  to view this content.

[YasserKhalil]

Hello Mr. Kenneth
I tested your code and it worked ... Just I have to open the Outlook after running the code...
I don't know why it doesn't send directly without the need of reopening the outlook .. But generally it is working well

[LJMetzger]

Yasser asked: How can I add signature parts? ...point me the name of the procedure and the line at which I need to add or edit to be able to follow changes

In post #6 See Sub SendEmailViaOutlook() in code Module ModOutlookEmail .

The best way to answer is through an annotated example. First we will start with a relatively simple working code example. To add to the simplicity, Outlook MUST be open before the code is run from Excel.

The example does the following:
a. Uses the Default Outlook Account as the sender
b. Uses the default Outlook Signature
c. Uses a simple HTML text message

Please Login or Register  to view this content.

Now to describe what various parts of the code are used for.
This opens Outlook only if it is already open using 'Late Binding'. For examples of 'Early Binding' vs 'Late Binding' see:http://www.excelforum.com/showthread.php?t=1020212
Early Binding is Excel version dependent and requires use of a VBA Library reference. If moving code between computers that use different versions of Office, the other computer must explicitly open the 'Library Reference' on the new computer.
Late Binding is version independent. However, if Outlook Constants are used, their values must be explicitly used.

For Outlook Constants and their values see: https://msdn.microsoft.com/en-us/lib...ffice.11).aspx

Please Login or Register  to view this content.

The Email body is saved in a separate string variable and can go anywhere in the code prior to the .Body or .HTMLBody statement that uses the variable. When using a Signature that contains something other than straight text, the Email body must use the HTML "<br>" as an EOL (End of Line) indicator. "<br>" is roughly equivalent to vbCrLf (Carriage Return - Linefeed) or vbLf (Linefeed) depending on the EOL convention of the computer. If the Body and Signature combination use Text only, the .Body statement is used and I use vbCrLf as the EOL character.

Please Login or Register  to view this content.

If the Body or Signature contains HTML (and/or picture in the Signature) , the .HTMLBody statement is used and "<BR>" must be used as the EOL character.

Please Login or Register  to view this content.

After the 'Outlook Application' object is created, a 'Mail Object' must be created. The 'Mail Object' references a specific account which is usually Account '0' (the Default Account). If Outlook has 5 different Email accounts, then the Email address with Account 4 can be used as shown below.

Please Login or Register  to view this content.

The 'Mail Object' does the rest of the work. There is no specific VBA command to get a signature, so a little trickery is required. If the Email body is BLANK the .Display command will display the Signature associated with the 'Mail Object' (a specific Email address). The signature can then be saved for later use.

Please Login or Register  to view this content.

Most of the following items are self-explanatory:

Please Login or Register  to view this content.

Combine the Email Body and the Signature

Please Login or Register  to view this content.

Add attachements if any

Please Login or Register  to view this content.

Finally,
a. To Display the Email in Outlook for Editing and review in Outlook:

Please Login or Register  to view this content.

or

a. To Send the Email to Outlook in the 'Send Queue'. This command DOES NOT send the message. Outlook is usually configured to Send/Receive periodically or to Send/Receive on Manual Commands by the User. The Email will be ACTUALLY SENT according to the Outlook setup.

Please Login or Register  to view this content.

To send the Email NOW is a topic for a completely different thread at some future time.

Lewis

[YasserKhalil]

Awesome Awesome Awesome. Thank you very much Mr. Lewis for this free and awesome tutorial
It is incredible in fact. I love it a lot
Thanks a lot for the time you spent to post that extremely fantastic explanation

Waiting for the future topic (Send the Email NOW)
Best and Kind regards

[LJMetzger]

Additional HTML information. When you want to use different fonts, font sizes, bold, color, etc., HTML code is needed, but you don't have to know HTML. There are several techniques that can be used in addition to typing the HTML from Scratch, including:

a. Using one of the many free HTML editors available for downloading including the one referenced from the above link (which I have not tried): http://www.html.am/html-codes/text/

b. Using a combination of Microsoft Word and your favorite text editor such as NotePad.
(1) Create the look and feel you want in Microsoft Word.
(2) Save the file as a .htm file
(3) Open the file using your favorite text editor
(4) Cut and paste the body text at the bottom of the .htm file into your VBA code.

Lewis

[YasserKhalil]

Thanks a lot Mr. Lewis for this additional and useful information
I think this is an easy way instead of learning html