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

  • Thread starter Thread starter Hii Sing Chung
  • Start date Start date
H

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.
 
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.
 
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.
 
Back
Top