Send out email through VBA and Macro in Outlook

[Jyggalag]

Dear all,

(Please note that I am using the Outlook program in Windows).

I currently have a setup that looks approximately like this (please ignore the snippet on the right side, it is purely to show what an email looks like in an outlook format):

I was wondering if it would be possible to create a VBA code that I could attach to the macro that I have in the top, where once I click it, it will send the email from my account ("Email sender") to the receiver ("Email recipient") with the possibility to add a CC and a BCC. The subject would then be whatever is written in the subject line on row 9 and the body of the email would be taken from the large merged box from row 12-42 (I expect to perhaps expand this to something like row 12 - row 80 to allow for larger emails).

Is it possible to create a VBA code that does this? And if yes, can somebody help me with it? It would truly save me a lot of trouble!

Thank you so much everybody! Your time is greatly appreciated and even if you cannot help, I appreciate the time that you spent on reading this forum and hopefully you too can use it if I find the correct answer

Kind regards,
Jyggalag

 

[Dan_W]

I notice that you've recently had some responses to your other related posts. In light of those responses, do you still need an answer to this thread?

 

[Jyggalag]

I notice that you've recently had some responses to your other related posts. In light of those responses, do you still need an answer to this thread?

Dear Dan,

Thank you very much for reading through my issue and bringing it to attention again. It is much appreciated!

I have for now written the email message in my VBA code. However, it would be much preferable to simply write it in a cell (or merged cells), given that this would make it much easier to edit the text for future use.

Best regards,
Jyggalag

 

[Jyggalag]

Dear Dan,

Thank you very much for reading through my issue and bringing it to attention again. It is much appreciated!

I have for now written the email message in my VBA code. However, it would be much preferable to simply write it in a cell (or merged cells), given that this would make it much easier to edit the text for future use.

Best regards,
Jyggalag

Note for anybody who might wonder, I used this code to write it in VBA:

Writing the text itself is then a bit difficult if you're not familiar with VBA in my opinion. I used strings and wrote my text in this sort of format:

Set EmailApp = New Outlook.Application

Dim EmailItem As Outlook.MailItem
Set EmailItem = EmailApp.CreateItem(olMailItem)

EmailItem.To = Join(Filter(Application.Transpose(Range("O2:O10")), " ", False), ";")
EmailItem.CC = "test2@mail.com"
EmailItem.BCC = "test3@mail.com"
EmailItem.Subject = "Test Email From Excel VBA"
EmailItem.HTMLBody =
EmailItem.HTMLBody = "Hello, this is Jyggalag</br>" & "<BR>" & _
"<b><u> This message has been written in bold with an underline </b></u> and now it is no longer in bold or with an underline</br>" & "<BR>" & _
"Thank you!"
Source = ThisWorkbook.FullName
EmailItem.Attachments.Add Source

EmailItem.Send

End Sub