Sending Email with Email body in a cell keeping the formatting.

[N_e_w_b_i_e]

Hi all,

I am new to the forum and VBA. All help is greatly appreciated. I am currently being tasked by my boss to do up a macro to send an email with the email body in a cell.

I have done my research and has found 2 macros that suit my needs however, i am having problems combining them. I am not sure what has gone wrong as i tried different combinations so that they can work.

I have attached a workbook here so every 1 has a easier time of viewing my code and my needs. Here are the links of the macros which i have used.

[gmayor]

The attached should work. Comments included in the code.

[N_e_w_b_i_e]

Hi gmayor,

I would like to thank you firstly for your swift reply. I really do appreciate it.

I have seen your macro but i need the auto bullet or numbering to be on. Your current macro is good, but my boss insists on having the auto bullet and number on as users are going to key in many more information in the form of bullets in that email. Do you have any other solutions ?

[N_e_w_b_i_e]

Hi all,

If you have any suggested solutions or workaround. Please feel free to reply in this thread. I thank all participants in advance for all the help rendered.

[gmayor]

Your request was for a macro that put your formatted cell as the body of the e-mail message, which is what I posted.

There was no mention anywhere of bullets and numbers, and there are no bullets or numbers in the cell that forms the body of the message.

[N_e_w_b_i_e]

Hi gmayor,

Please pardon me for not being clear as this is the first time i am using a forum to ask questions. Will you be able to provide a solution or identify where i have gone wrong in the macro which i have created ?

I am creating a macro that is able to send the text and format in a cell out as the email body. At the same time, the functions of outlook is still available as users would have to input information in bullets or numbering form. Hence, you can see in my macro that i searched for a convert text to html function and am now trying to combine the function and macro.

I am currently having issues which i believe is the function fnConvert2HTML(strbody) under the .HTMLBody. Do you have any idea where i might have gone wrong ? Did i declare anything wrongly?

Once again, i thank you in advance for your solutions or suggestions.

[N_e_w_b_i_e]

Please feel free to ask me for the links where i copy the macros from if you need the source.

[mdmackillop]

Excel does not contain the formatting you seem to be after. Insert a Word Object into Excel where you can use word formatting and which can be copied and pasted into your email

Sub Mail_small_Text_Outlook()
'For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm
'Working in Office 2000-2016
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String


Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)


ActiveSheet.Shapes.Range(Array("Object 1")).Select
Selection.Copy


On Error Resume Next
With OutMail
.To = "ron@debruin.nl"
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.BodyFormat = olFormatRichText
Set editor = .GetInspector.WordEditor
editor.Content.Paste
.Display
End With
On Error GoTo 0


Set OutMail = Nothing
Set OutApp = Nothing
End Sub

[N_e_w_b_i_e]

Hi mdmackillop,

Thanks for the advice and solution. I have copied your code and try to run it but however i encounter a problem at "ActiveSheet.Shapes.Range(Array("Object 1")).Select". I believe i need to set object 1 to a range in the Sheet1. I tried selecting B5 and B5:C5 but there were errors. Would you kindly advice me on how to use your codes? I am sorry but i am new to VBA.

[Kenneth Hobs]

Did you?
Insert a Word Object into Excel where you can use word formatting

To do so, Insert > Object > Microsoft Word Document > OK. Click outside of object to deselect it. Select it again and notice that Name in the upper left near A1 will be "Object 1" if that is your first object. Of course you can change the name there and in the code if needed.

This means that data is in the Word object, and not a cell. Your users will need to be able to know how to add data to the object. You can add a button to open the object for the user if needed.

[mdmackillop]

Did you see this?
Insert a Word Object

[gmayor]

I am creating a macro that is able to send the text and format in a cell out as the email body. At the same time, the functions of outlook is still available as users would have to input information in bullets or numbering form.
Did you try the attachment I posted? It does what you ask. It opens a message with your cell content formatted as the body of the message (with or without the signature associated with the account). The message is in HTML format and as posted will open the message on screen with the content from the workbook. If you then want to add bulleted text to that message body then put the cursor where you want the bulleted paragraph and click the bullet option on the Message tab and a bullet will be added to the current paragraph.

20249