Date from Excel into Outlook

Syed Iqbal

New Member
Joined
Sep 15, 2002
Messages
5
Hi, I am new here.
I have a incoming time sheet in Excel with data of each month in rows.
I have created a macro which can send emails to all the intended users.
The problem is that I am not managing to copy the selected row (text) as a body text of outlook message.
Any suggestion are welcomed.
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Syed Iqbal

New Member
Joined
Sep 15, 2002
Messages
5
On 2002-09-16 02:57, Andrew Poulsom wrote:
Unfortunately SendMail does not have a Body argument, so you will need to use Outlook VBA.

See this link:

http://www.mrexcel.com/board/viewtopic.php?topic=20793&forum=2

First of all thanks to Dennis and Andrew for their prompt responses.
Guys, the following code is working with a little problem that it sends every user two emails, one with attachement and one with a blank message in body text. I have checked the attachment is fine with a INCOMING DATE of only the intended employee (controlled through an array variable and a loop).
I am quite sure, I will also get a hold of it with God's help.

Sub post()

Dim objOutlook
Dim objNameSpace
'
Dim mItem

Dim strReceipient
Dim strSubject
Dim strBodyText

Dim myMaster
myMaster = ActiveWorkbook.Name
Const olMailItem = 0

Dim rece
rece = Array("Syed Iqbal", "Zahid Qamar", "Ishtiaq Khan", "Aamir Zakaria")


For q = 0 To 5

strReceipent = rece(q)

strSubject = "Incoming time report from 1 September to 15 September 2002"
'or you can use the specific cell address which is a variable data

With ActiveSheet.Range("b:b")
Set xFind = .Find(rece(q), LookIn:=xlValues, LookAt:=xlWhole)
Range(xFind.Address).Select

If Not xFind Is Nothing Then
FstOccurance = xFind.Address
Do
Set xFind = .FindNext(xFind)
Loop While Not xFind Is Nothing And xFind.Address <> FstOccurance
End If
End With
Dim a

a = Selection.EntireRow.Address

Range("5:5," & a).Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Columns("A:A").EntireColumn.AutoFit
Columns("B:B").EntireColumn.AutoFit

ActiveWorkbook.SendMail (rece(q))


Set objOutlook = CreateObject("Outlook.application")
Set objNameSpace = objOutlook.GetNamespace("MAPI")
Set mItem = objOutlook.CreateItem(olMailItem)


mItem.To = strReceipent
mItem.Subject = strSubject

mItem.Save
mItem.Send

Workbooks("myMaster").Activate

If strReceipent = "" Then
MsgBox "No Receipient provided. Mail not sent", vbInformation, "Mail Error"
End If

Next q

' **** Clean up
'
Set mItem = Nothing
Set objNameSpace = Nothing
Set objOutlook = Nothing

End Sub
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

That's because you're using the SendMail method AND Outlook VBA to send the message.

Delete the SendMail line and:

1. Use the Body property in Outlook to add your message:

Code:
mItem.Body = strBodyText

2. Use the Add method of the Attachment object to attach your file (I think you will need to save it first):

Code:
MItem.Attachments.Add ActiveWorkbook.Name

Here's an extract from Outlook VBA Help:

Add Method (Attachments Collection)

Creates a new attachment in the Attachments collection, and returns the new attachment as an Attachment object.

Syntax

objAttachments.Add(Source, [Type], [Position], [DisplayName])

objAttachments Required. An expression that returns an Attachments collection object.

Source Required Variant. The file (represented by the full path and file name) or item that constitutes the attachment.

Type Optional Long. The type of attachment. Can be one of the following OlAttachmentType constants: olByReference(4), olByValue(1), or olEmbeddedItem(5).

Position Optional Long. The position of the attachment within the body text of the message.

DisplayName Optional String. The display name of the attachment. Ignored unless Type is set to olByValue.

Remarks

The following table describes the purpose of each OlAttachmentType constant value.

Constant Use to
olByReference Create a shortcut to an external file
olByValue Embed attachment in the item
olEmbeddedItem Create a shortcut to an Outlook item

When an Attachment is added to the Attachments collection of an item, the Type property of the Attachment will always return olOLE(6) until the item is saved.

To ensure consistent results, always save an item before adding or removing objects in the Attachments collection of the item.

Example:

This Visual Basic for Applications example uses CreateItem to create a new mail message, attaches a Microsoft Excel workbook as an attachment (not a link) using the Attachments property and gives the attachment a descriptive caption.

Code:
Set myOlApp = CreateObject("Outlook.Application")
Set myItem = myOlApp.CreateItem(olMailItem)
Set myAttachments = myItem.Attachments
myAttachments.Add "C:My DocumentsQ496.xls", _
    olByValue, 1, "4th Quarter 1996 Results Chart"
 

Syed Iqbal

New Member
Joined
Sep 15, 2002
Messages
5
Thanks Andrew, for pointing out the loop hole, but if you wish to send the desired employee's data not as an attachment but as a body text, I mean, if you wish to directly copy your desired range in the message body,

is it possible? Please reply soon.

On 2002-09-16 03:37, Andrew Poulsom wrote:
That's because you're using the SendMail method AND Outlook VBA to send the message.

Delete the SendMail line and:

1. Use the Body property in Outlook to add your message:

Code:
mItem.Body = strBodyText

2. Use the Add method of the Attachment object to attach your file (I think you will need to save it first):

Code:
MItem.Attachments.Add ActiveWorkbook.Name
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Sorry, I don't know. Outlook doesn't have a macro recorder to give me a clue and help won't tell me either.

Perhaps someone else knows.
 

dk

MrExcel MVP
Joined
Feb 15, 2002
Messages
2,942
Hi,

Look at the link that XL-Denis posted. It shows you how to send an email through Outlook with the body of the message being a specified range.

If you have any problems adapting the code then post back and either me or someone else should be able to help.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,583
Messages
5,597,023
Members
414,116
Latest member
sfullnet

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top