I've taken some code from Ron De Bruins excellent E-mailing from Excel, but
have a couple of questions that as a novice I can't figure out. The code I'm
using is for Outlook only and is (slightly tweaked)

Q 1 - If I wish to place in the "To" a variable, such as a cell reference
(because this cell's value may change) how can I place that within the code.
Say Sheet1 A10
Q2 - SImilar to Q1, how can I place in the "Subject Line" a variable cell
reference, say Sheet1 B10

Thanks


Sub Mail_Outlook()
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Dim wb As Workbook
Dim strdate As String
strdate = Format(Now, "dd-mm-yy h-mm")
Application.ScreenUpdating = False
Sheets("E-Schedule").Visible = True
Sheets(Array("E-Schedule")).Copy
Set wb = ActiveWorkbook
With wb
.SaveAs ThisWorkbook.Name _
& "Sent on" & " " & strdate & ".xls"
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.To = "
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = "Hi there"
.Attachments.Add wb.FullName
.Send 'or .Display
End With
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

Hi John

Click on the Tips link that is on every example page
http://www.rondebruin.nl/mail/tips2.htm

--
Regards Ron De Bruin
http://www.rondebruin.nl



"John" wrote in message ...
I've taken some code from Ron De Bruins excellent E-mailing from Excel, but have a couple of questions that as a novice I can't
figure out. The code I'm using is for Outlook only and is (slightly tweaked)

Q 1 - If I wish to place in the "To" a variable, such as a cell reference (because this cell's value may change) how can I place
that within the code. Say Sheet1 A10
Q2 - SImilar to Q1, how can I place in the "Subject Line" a variable cell reference, say Sheet1 B10

Thanks


Sub Mail_Outlook()
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Dim wb As Workbook
Dim strdate As String
strdate = Format(Now, "dd-mm-yy h-mm")
Application.ScreenUpdating = False
Sheets("E-Schedule").Visible = True
Sheets(Array("E-Schedule")).Copy
Set wb = ActiveWorkbook
With wb
.SaveAs ThisWorkbook.Name _
& "Sent on" & " " & strdate & ".xls"
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.To = "
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = "Hi there"
.Attachments.Add wb.FullName
.Send 'or .Display
End With
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
Set OutMail = Nothing
Set OutApp = Nothing
End Sub