I need to write macro in Outlook97 to auto send e-mail w/attachment 1

[MrsTFB]

Hey guys,
I'm trying to write VBA code that will automatically send an e-mail. I have code that does send a message with subject and other text found within the current notebook. It works beautifully. However, now they want to include an attachment, an Excel file. The name of the file will he held in another workbook. I think that could be done with range("az44&quot.text

Have any of you guys done anything similar? Also, the one currently being used only send to an internal internet system. The new one will use the Explore for an external address.

Would appreciate any guidance in this area.

Thanks in advance,

mrsTFB in Tennessee

 

[KyleS]

MrsTFB,
Could you post the code you're using now, so we can see where you're going and what we'll need to add... Also, which appliction is the code being written in?

I would think you could simply open the excel document and retrieve the path as you stated above: Range("az44&quot.text

strPath = Range("az44&quot.text '(you don't really need the "text&quot

And then use your Outlook item

With Outlookmsg (or whatever your variable name is)
.Attachments.Add strPath,, olByValue, 1
End With

Kyle

 

[MrsTFB]

Here's the code: It's a little long The coloring is mine, for distinction.
Application.StatusBar = "Preparing and delivering e-mail notification"

Unload CLCpword
Sheets("Sign-Off Record&quot.Select
Range("e20&quot.Value = "CLC"
Range("d20&quot.Value = TextBox2.Text
Range("E5:e8&quot.Select
Selection.Copy



Range("ax1&quot.Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

ActiveWorkbook.Save


'Dimension variables.

Dim OL As Object, MailSendItem As Object
Dim W As Object
Dim MsgTxt As String, SendFile As String
Dim ToRangeCounter As Variant
Dim RFQNum As String



'Pulls text from file for message body

MsgTxt = "Chris Cochran has completed his portion of Quote # " + Range("Ax2&quot.Text + " Target Date: " + Range("ax3&quot.Text + " Customer: " + Range("ax1&quot.Text + " Description: " + Range("ax4&quot.Text

'Ends Word session
'Set W = Nothing

'Starts outlook session
Set OL = CreateObject("outlook.application&quot
Set MailSendItem = OL.createitem(olMailItem)

ToRangeCounter = 0

'Identifies number of recipients for To list.
For Each xCell In ActiveSheet.Range(Range("tolistCLC&quot, _
Range("tolistCLC&quot.End(xlToRight))
ToRangeCounter = ToRangeCounter + 1
Next xCell

If ToRangeCounter = 256 Then ToRangeCounter = 1

'creates message
With MailSendItem
.Subject = ActiveSheet.Range("subjectcell" + " " + RFQNum).Text
.body = MsgTxt

'creates "TO" list
For Each xRecipient In Range("tolistCLC&quot.Resize(1, ToRangeCounter)
RecipientList = RecipientList & ";" & xRecipient
Next xRecipient

.to = RecipientList
.Send

End With

'ends outlook session
Set OL = Nothing

Application.CutCopyMode = False


There may have been a better way to do this, but I am not very great at VBA and this works.

Thanks for looking at it.

mrsTFB in Tennessee

 

[KyleS]

OK, so you're doing this in excel, now, is the file path in the workbook you're using to get the body text? If so all you have to do is put the red line in

'creates message
With MailSendItem
.Subject = ActiveSheet.Range("subjectcell" + " " + RFQNum).Text
.body = MsgTxt

'creates "TO" list
For Each xRecipient In Range("tolistCLC&quot.Resize(1, ToRangeCounter)
RecipientList = RecipientList & ";" & xRecipient
Next xRecipient

.to = RecipientList
.Attachments.Add Range("AZ44&quot.Text,, olByValue, 1
.Send

End With

Otherwise you'll need to open up the workbook and get the file path...(Let me know if we need to...)


As a side note, put this line right before "Set OL = Nothing"

Set MailSendItem = Nothing
Kyle

 

[MrsTFB]

Thanks for looking at this, as you can see, in my previous use of this, I named text ranges in my Excel workbook for the RecipientList. Now though I am going to be sending to an external address, such as joeblow@somewhere.com

Will that matter or can I simply type in the external address.

Also, at this point, I do think I will have the workbook that will be the attachment name in the workbook that is open.

Thanks again, I'm going to start playing with this now.

mrsTFB

 

[KyleS]

The address won't matter

.Recipient = "Steve@Steve.Com" will work fine.

So your Range("AZ44&quot.Text will be in another workbook? OK, then you have 2 options:

1) open up the other workbook, read the range you want then close it:

Workbooks.Open Path here
.Attachments.Add Range("AZ44&quot.Text,, olByValue, 1
Excel.ActiveWorkbook.Close

Or you could select a range somewhere in your sheet you're currently working in that you know will be blank ("IZ6550&quot and set it's formula:

Range("IZ6550&quot.Formula = PathName & " Range("AZ44&quot.Text
.Attachments.Add Range("IZ6550&quot.Text,, olByValue, 1


either way... Kyle

 

[MrsTFB]

Kyle,
You are my new hero!!! I added the .Attachments.Add Range("AZ44&quot.Text,, olByValue, 1 . It gave me an error message on the olByValue, so I just took that out and it works beautifully.

I have referenced the information in my other workbook with a simple = and the path for two of my items. The filename and month ending date. I named the subjectcell and addresscell ranges in my workbook and it is doing exactly what I wanted.

You guys make this stuff so EASY! you have earned a double star today!

mrsTFB

 

[KyleS]

Glad I could be of some help... And thanks for the star! Kyle