0

I have a VBA that copies the details from an excel sheet to a outlook mail body: Below is the macro:

Sub Send_Email()

Dim EmailSubject As String
Dim SendTo As String
Dim EmailBody As String
Dim ccTo As String

EmailSubject = "Test"
SendTo = "superuser@superuser.com"


FirstRow = 1 
LastRow = 5
FirstCol = 1 
LastCol = 2 

For r = FirstRow To LastRow
For c = FirstCol To LastCol

For Each cell In Cells(r, c)
strtable = strtable & "  " & cell.Value
Next
Next
strtable = strtable & vbNewLine
Next

EmailBody = "Hi" & vbLf & vbLf & " body" & vbLf & vbLf & strtable & vbNewLine 

Set App = CreateObject("Outlook.Application")
Set Itm = App.CreateItem(0)
With Itm
.Subject = EmailSubject
.To = SendTo
.CC = ccTo
.Body = EmailBody
.Display
'.Send
End With
Set App = Nothing
Set Itm = Nothing
End Sub

But the details on the excel sheet are in a tabular format, with some formatting. Can the table and formatting be retained, when data is copied into outlook? If Yes, how?

  • You could try, for instance, slecting the range, using the copy method to clipboard, and then, instead of setting the body, you use sendkeys to "ctrl+v" (^v). There may be a safer/better/programmatic way to paste instead of a key macro – Yorik Dec 11 '15 at 17:13
  • If you want the table, why not just take the table instead of recreating it? – Raystafarian Dec 11 '15 at 17:34
  • How to copy the table? Can you show me the places where i need to make changes in the code,and what methods to use? @Yorik – LoneWolf91 Dec 12 '15 at 12:53
  • How to copy the table @Raystafarian – LoneWolf91 Dec 12 '15 at 13:08
  • I don't understand your code, is it in outlook or in excel? What variables target excel? – Raystafarian Dec 13 '15 at 9:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.