E-Mailing Question

[John]

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 = "[email protected]"
.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

[Ron de Bruin]

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" <[email protected]> wrote in message news:[email protected]...
> 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 = "[email protected]"
> .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
>
>