Need VBA code to Send Formatted Table from Excel via Email using Outlook

[alchavar]

hello there,

Can someone please help me?

I'd like to automate with VBA sending an email to multiple recipients.
The email's body must say the text below and then copies and pastes the formatted table you see on the attachment (Columns A to H):

Hello All,

Month End is finally here. Please find below summary of your missing hours.

As there are multiple recipients, I'd like to email to be sent from the Bcc: field so replies are not sent to all recipients.

The Subject on the email must read:

***You have Missing Time***

Do you think that's possible to do? The recipients' email addresses show up on column I.

[maniacb]

Do you want to send the email in Outlook or something like gmail? It is very possible to do this.

[alchavar]

hi maniacb,

Thanks for asking. If possible, I'd like to send the email from Excel without having to open my Outlook application.

Also, just to let you know: most users are currently using Outlook as that's my company's software. But as we have contractors working for us, I want some of them to receive the email at their personal email address also indicated on Column I.

Hope that makes sense.

[maniacb]

Do you want to add a column with the date value of when the email was created? So that you know if it was sent?

[alchavar]

That would be awesome!!! Yes, please. If possible, can I also receive the email? Let's put my personal email on the code for now. I'll change it later. The email is: [email protected]

[maniacb]

Here is the code. and the working file. Email also sent

Please Login or Register  to view this content.

[alchavar]

hi maniacb,

Can you adjust the code a bit? The code is creating draft emails but is not sending them over to the recipients.

Also, instead of sending multiple emails (one to each recipient), could we just send one email with the whole table embedded on it?

And lastly, can we put the body of the email on a separate row? It currently shows this:

Hello All, Month End is finally here. Please find below summary of your missing hours.

Can we make it like this?

Hello All,

Month End is finally here. Please find below summary of your missing hours.

[maniacb]

Also, instead of sending multiple emails (one to each recipient), could we just send one email with the whole table embedded on it?

On this point, which email o you want to send the one email to? The first one in the line, so there is no loop, or do you want to send the whole table to each email?

[alchavar]

hi manicb,

Correct. My intention is to just send one single email. So all the email addresses on column I should be on the Bcc: field in Outlook. All recipients will get the full table (not just the row with their missing time).

Also, can you put my email address on the Cc: field? [email protected]

[maniacb]

Here you go. The email will be sent without first displaying it

Please Login or Register  to view this content.

[alchavar]

hi maniacb,

Thank you so much.

One more thing:

Attachment 700745

As you can see, I have to manually send the email. Can you modify the code one last time so that:

-I don't have to see the email displayed in Outlook
-the email is sent automatically from Excel (me not having to click Send on Outlook)?

[maniacb]

Your attachment didn't come through. Attachments work on the second attempt on this site. Try it again

[alchavar]

Sorry for that! Let me know if you get it this time.

I had to insert is an image because the option for attaching files I no longer see it activated.

Attachment 700761

[maniacb]

to attach a file, select the Go Advanced option

[alchavar]

Thank you. Here you go!

[maniacb]

The code as written in post #10 should not be displaying the email. Try deleting the line below and re-running the code.

'.Display 'DELETE THIS LINE IF USING SEND

[alchavar]

I am afraid is not working. I deleted the line as you said and it didn't do anything. Can you try sending it to yourself and see if it works for you? Here's the Excel file with the code from post #10 without the line '.Display 'DELETE THIS LINE IF USING SEND

[alchavar]

Sorry. Forgot to attach the file.

[maniacb]

On your code, the send line has a an apostrophe in front. Remove that apostrophe.

so the code should look like:

Please Login or Register  to view this content.

[alchavar]

It worked! Thank you so much.

One last thing (I promise): how can I create a new line on the body so that the greeting and the rest of the message show on separate lines like this?:

Hello All,

Month End is finally here. Please find below summary of your missing hours.


The email is being sent with one single line:

Hello All, Month End is finally here. Please find below summary of your missing hours.

[alchavar]

Sorry! Forgot to ask you: I generate this report from another workbook that has multiple worksheets. If I want to embed the code that you so kindly generated for me, how can I make it suitable for that spreadsheet on that workbook? Let's say that the name of the worksheet where the report is found is Missing Time Report.

[maniacb]

I updated the code to work on any sheet, the activesheet. But it assumes all the cells are in the same position.

Please Login or Register  to view this content.

As for the initial body data all being on one line, Let me think about that.

[alchavar]

Great! It worked. Thank you so much. Now just awaiting the fix to break the body into 2 lines. Take your time. No rush!

[maniacb]

Try this

Please Login or Register  to view this content.

[alchavar]

I did, but it's still not working.

[maniacb]

Let's try a different approach

Please Login or Register  to view this content.

[alchavar]

Unfortunately, it's still not working. I got the same result as on post #25.