macro to copy cells from spreadsheet in Outlook email

[marinica]

Hi guys,

how can you write a macro to copy cells from spreadsheet and paste them in the body of an Outlook email?

 

[daverunt]

I am sure there are other methods but I concatenate cells to do this.
You need to enter the first/last Row, first/last column.
(You could code for dynamic rows/column numbers)

Sub Send_Email()
 
Dim EmailSubject As String
Dim SendTo As String
Dim EmailBody As String
Dim ccTo As String
 
EmailSubject = "Test"
SendTo = "[EMAIL="test@yahoo.co.uk"]test@yahoo.co.uk[/EMAIL]"
 
 
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 of message you want to add" & 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

 

[marinica]

Hi daverunt,

Thank you very much.

It worked and I needed to adjust a bit the formating.

I am wondering if you actual can copy a part of spreadsheet and paste it in Outlook as a picture (or as table with the same format as you have set up in EXCEL)

cheers

 

[daverunt]

Hi

I forgot I did a concatenated version here with HTML tags so the cell spacing is tabulated .

http://www.mrexcel.com/forum/showthread.php?t=551464

Also look at RangeToHTML, which i haven't used.
http://www.rondebruin.nl/mail/folder3/mail4.htm


Not sure about copying/pasting as pic.

 

[marinica]

Hi Daverunt,

Thank you

It is looking much better now.

Cheers

 

[Jon von der Heyden]

I had to delete the last two posts because of trouble with the HTML that I couldn't fix.

 

[daverunt]

Sorry about that John, I forgot the HTML tags and used code tags and it threw a wobbly.

The gist of the post was just about adding a border to the table in Outlook.

 <table Border =1>