send e-mail from Excel, copy and paste at specific place in e-mail

[Hii Sing Chung]

Dear experts,


I am writing VBA in Excel to try to automate the daily reporting of sending
data from Excel through e-mail. I got stuck at getting Excel to paste the
clipboard data into the right place in e-mail. The codes:
Sub SendDailyReport()
Dim objOutlook As Outlook.Application
Dim Doc As Word.Document
Dim wdRange As Word.Range
Dim objEmail As Outlook.MailItem
Dim wbBook As Workbook
Dim wsSheet As Worksheet

Application.ScreenUpdating = False
Set objOutlook = GetObject(, "Outlook.Application")
If objOutlook Is Nothing Then
Set objOutlook = New Outlook.Application
objInbox.Display
End If

Set wbBook = ThisWorkbook
Set wsSheet = ThisWorkbook.Worksheets("reporting")
wsSheet.Activate
With ActiveSheet.UsedRange
..Copy
End With

Set objEmail = objOutlook.CreateItem(olMailItem)

With objEmail
..Importance = olImportanceHigh
..Subject = "Daily Report " & FormatDateTime(Date, vbLongDate)
..To = "Senior Managers"
..Body = "Dear Sir, " & vbCrLf & vbCrLf & _
"Daily Report As Show Below:" & vbCrLf & vbCrLf & vbCrLf & _
vbCrLf & vbCrLf & "Thanks and Best Regards," & vbCrLf & vbCrLf & _
"Security Group" & vbCrLf & "Extension Number: 27555"
..Display
Set Doc = objEmail.GetInspector.WordEditor
Set wdRange = Doc.Range
wdRange.Paste
End With
Set wsSheet = ThisWorkbook.Worksheets("chart")
wsSheet.Range("A19:AD59").Copy
objEmail.Display
wdRange.Paste
Application.CutCopyMode = False 'clear the clipboard
ExitSub:
Set objNewMail = Nothing
Set objInbox = Nothing
Set objNameSpace = Nothing
Set objOutlook = Nothing
Exit Sub

As of now, the paste into e-mail will overwrite whatever is already there. I
want the first paste (from worksheet "reporting") to paste right after the
"Daily Report As Show Below:" line and the 2nd paste (from worksheet
"Chart") to paste right below the first pasted data.

How do I manipulate the range or cursor so that I can paste at the right
place? Any help is very much appreciated.

 

[Ron de Bruin]

Try
http://www.rondebruin.nl/mail/folder3/mail4.htm

Read also the Tip below the macro

 

[Hii Sing Chung]

Dear Ron,
Thank you for your message. I've read the codes and the tip but still it
doesn't do exactly want I wanted. I want to be able to insert the data from
the clipboard (which is copied from Excel) into the correct place, a
position in between the salutation and the signature in Outlook e-mail, and
then follow by another copy-and-paste from Excel into the e-mail at the
position right after the first paste. I must use Word for e-mail editing,
that is for consistency.

 

[Ron de Bruin]

must use Word for e-mail editing

I think the best way is to create a new sheet with code and copy all the stuff you
want with code on that sheet and mail it and delete it after that

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

 

[Hii Sing Chung]

Thanks Ron,

As the format of the e-mail is defined by the recipients (the Senior
Management), it has to be done that way, cannot be just send the spreadsheet
to the recipients.
Right now what I do is to put all the text into the Excel worksheet itself
and the chart portion also put a copy into this worksheet and when pasting
into e-mail, it pastes once and everything. That serves the purpose but I am
still researching on how to paste into the correct position or any position
I want.