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.

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

Read also the Tip below the macro



"Hii Sing Chung" wrote:

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.

"Ron de Bruin" wrote in message
...
Try
http://www.rondebruin.nl/mail/folder3/mail4.htm

Read also the Tip below the macro



"Hii Sing Chung" wrote:

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.

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" wrote in message ...
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" wrote in message
...
Try
http://www.rondebruin.nl/mail/folder3/mail4.htm

Read also the Tip below the macro



"Hii Sing Chung" wrote:

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.

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.

"Ron de Bruin" wrote in message
...
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" wrote in message
...
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" wrote in message
...
Try
http://www.rondebruin.nl/mail/folder3/mail4.htm

Read also the Tip below the macro



"Hii Sing Chung" wrote:

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.