Macro for sending e-mail with info from an excel sheet

[VDU]

Hello,

I'm not sure if this is even possible but if it is, it would be great.

I'm trying to make a macro that sends an e-mail based on information present
in an excel sheet but does not send the excel file as attachment (like the
"sendmail" method)

The perfect solution would be something like the "Send to mail recipient"
option but I cannot seem to get that from VBA (tried recording to see what
happens in VBA when I use that option, doesn't record anything).

The problem in short is this:

I have to create a macro that opens a new mail message window filled with:
- the contents of cell L22 (1 to 10 names separated by ";") in the "To" field
- the contents of cell L23 (1 to 10 names separated by ";") in the "CC" field
- the contents of cell B24 in the "Subject" field
- the contents of cells A29 to L51 in the "Body" field keeping the format of
the tables and colored cells)
- if the macro can resolve the names in to and cc (equivalent to Ctrl+K) it
would be great but this is not crucial.

In long:

I have a sheet containing a form where information can be entered, the sheet
processes this information to a different format which I need to send via
e-mail. this can be done perfectly by "send mail to recipient" but I need to
have the To, CC and Subject fields filled automatically with info from the
same sheet as well which cannot be done with the above option.

The sheet contains:
- Data area, where raw data is added and calculated
- Mail info area containing a cell with names used to determine addresses
for the "To" field separated by ";" , a cell for the "cc" field same as for
"To" and 1 cell containing the subject (determined by a formula using data
from the data area)
- mail body area containing the text & tables to be added to the mail body.
Values are determined using formulas for either calculation or reference to
cells in the data area.

There are 2 incomplete solutions for this problem, which could complement
together great if I would find a way to make them work together.

The first one would be the "send mail to recipient" option which uses the
body (given mail body area has it's own sheet) but does not automatically
fill the TO, CC and Subject fields of the mail

The second one is a post called "Send email based on cell value" which can
fill the To and CC and subject fields using data from the sheet and I managed
to adapt Ron de Bruin's solution a bit but I got stuck at keeping the
formatting for the body.

Please help, thank you.
Valentin

 

[John Bundy]

The Mail worksheet in the body on Rons site won't work for you? You can make
any of the fields "automatic"
http://www.rondebruin.nl/mail/folder3/mail2.htm
where you see the .to= you can replace that with any name, or from a cell, so:

With OutMail
.To = Sheet1.Range("L22").Text
.CC = Sheet1.Range("L23").Text
.BCC = ""
.Subject = Sheet1.Range("B24").Text
.HTMLBody = RangetoHTML(rng)
.Send 'or use .Display
End With

 

[Ron de Bruin]

For the OP

See also
http://www.rondebruin.nl/mail/tips2.htm

 

[VDU]

Hello,

The problem I had with Ron's solution was the HRML formatting because I have
to send a table which, I didn't try, but I imagine doesn't fit too well into
a string type variable (strbody) as recommended, still, this worked ok with
the .to and .cc field.

I checked the link you sent though and there is a method to send HTML
formatted mails, I can't try now but this should work. I'll let you know if
it doesn't, until then, thank you.

 

[Ron de Bruin]

You can also try this
http://www.rondebruin.nl/mail/folder3/mailenvelope.htm