Date from Excel into Outlook

[Syed Iqbal]

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.

 

[Andrew Poulsom]

What code are you using to send the EMail?

 

[XL-Dennis]

Syed,

Welcome to the board

That´s a rather complex target You like to achieve.

However, a search in archieve resulted in following interested thread:

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

Kind regards,
Dennis

 

[Syed Iqbal]

On 2002-09-16 02:33, Andrew Poulsom wrote:
What code are you using to send the EMail?

ActiveWorkbook.SendMail ("test@yahoo.com")

 

[Andrew Poulsom]

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

 

[Syed Iqbal]

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]

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:

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):

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.

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]

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:

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):

MItem.Attachments.Add ActiveWorkbook.Name

 

[Andrew Poulsom]

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]

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.