Code to email from Excel 97 1

[Tiglet]

I want to be able to send emails to specified recipients, with subject line, message and attachment. I have written the following macro which extracts data from an excel list and does all of the above except adding a message and I don't know how to.
Any ideas?
PS Running Novell Group Wise & Excel 97

Thanks

Sub Auto_email()
'
' Auto_email Macro
' Macro recorded 12/04/02


Dim strThisCC As String, strSheetName As String
Dim recipients As String, subject As String
Do While True
strThisCC = ActiveCell.Value
If strThisCC = "" Then Exit Sub
'If reaches end of list then stop macro
ActiveCell.Offset(0, 1).Activate
'move to next column which lists the recipients
recipient = ActiveCell.Value
ActiveCell.Offset(0, 1).Activate
'move to next column which lists the file names
subject = ActiveCell.Value
strSheetName = strThisCC & "_Budget2003.xls"
'open up next file
Workbooks.Open (strSheetName)
ActiveWorkbook.SendMail recipient, subject
ActiveWorkbook.Close
ActiveCell.Offset(1, -2).Activate
'go to next set of details on the list in excel
Loop
End Sub

 

[DaleWatson123321]

Tiglet,

This is not a DIRECT solution, but coincidentally, I just finished emailing a couple of example files in response to another post in Tek-Tips – thread68-249743.

Because of the similarity, I thought you could perhaps ALSO benefit from these files. If you would like them, just email me and I’ll send them via return email.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[Tiglet]

I have played around with all sorts of bits and pieces and I think a Direct solution is the only thing that is going to help as my knowledge base is pathetically small!

 

[RobBroekhuis]

You'll have to work through Outlook to do what you're trying to do. The code will look fearsome (what with Outlook employing such obscure objects as NameSpace), but will do what you need to do - but with a good bit more code than just a SendMail method...
I suggest you post in the VBA forum if you're interested in this approach.
Rob

 

[LSTAN]

Hi Tiglet

Maybe you want to try this code; it's uses Outlook as the email client.

Sub Send_EMail()

Dim myRow As Integer, myCol As Integer, MyBodyText As String
Dim objOL As New Outlook.Application
Dim objMail As MailItem

Set objOL = New Outlook.Application
Set objMail = objOL.CreateItem(olMailItem)

myRow = ActiveCell.Row
myCol = ActiveCell.Column
'text for the mail body
MyBodyText = ""
MyBodyText = MyBodyText & "Hi, " & ActiveSheet.Cells(myRow, myCol) & "," & vbCrLf & vbCrLf
MyBodyText = MyBodyText & "This is to inform you that your delivery will be on "
MyBodyText = MyBodyText & ActiveSheet.Cells(myRow, myCol + 2).Text & "." & vbCrLf & vbCrLf
MyBodyText = MyBodyText & "LSTAN" & vbCrLf

With objMail
.To = ActiveSheet.Cells(myRow, myCol + 1)
.Subject = "No Subject"
.Body = MyBodyText
.Display
End With

Set objMail = Nothing
Set objOL = Nothing

End Sub


NB: Remember to create a reference to the Outlook Object Library

Hope this will helps.

rgrds
LSTAN

 

[Tiglet]

Thanks for the suggestions. Unfortunately, I can't use Outlook it has to be Novell groupwise.