Hi all,
I am trying to write a macro sub that will use named ranges from my sheet as values in the subject lines and other places. I seem to be having trouble with the code suggested in 2002 excel book and also in this forum. Here is what I have so far with some Rem lines showing variations I have tried. Any help is welcomed. I seem to be missing something here. I have tried activating the sheet to allow the macro to call the values but that gives me error when I walk through the code.
Sub SendEmail()
' Application.ActiveWorkbook.Worksheets("Request"
.Activate
ESubject = "Request for " & ActiveWorkbook.Names(LastName).Value & ", " & ActiveWorkbook.Names(FirstName).Value & " " & ActiveWorkbook.Names(GroupName).Value
' ESubject = "Request for " & Range("Firstname".Value
SendTo = Range(SupName).Value & "@mycompany.com"
Ebody = "Testing VBA's ability to send an email."
NewFileName = "C:\My Documents\TestFile.xls"
Set App = CreateObject("Outlook.Application"
Set Itm = App.CreateItem(0)
With Itm
.Subject = ESubject
.To = SendTo
.Body = Ebody
'.Attachments.Add (NewFileName) ' Must be complete path
'.Display ' This property is used when you want
' the user to see email and manually send. Then
' comment out rest of code except “End With” statement
' and "End Sub" statement.
.send
End With
Set App = Nothing
Set Itm = Nothing
End Sub
I am trying to write a macro sub that will use named ranges from my sheet as values in the subject lines and other places. I seem to be having trouble with the code suggested in 2002 excel book and also in this forum. Here is what I have so far with some Rem lines showing variations I have tried. Any help is welcomed. I seem to be missing something here. I have tried activating the sheet to allow the macro to call the values but that gives me error when I walk through the code.
Sub SendEmail()
' Application.ActiveWorkbook.Worksheets("Request"
.ActivateESubject = "Request for " & ActiveWorkbook.Names(LastName).Value & ", " & ActiveWorkbook.Names(FirstName).Value & " " & ActiveWorkbook.Names(GroupName).Value
' ESubject = "Request for " & Range("Firstname"
.ValueSendTo = Range(SupName).Value & "@mycompany.com"
Ebody = "Testing VBA's ability to send an email."
NewFileName = "C:\My Documents\TestFile.xls"
Set App = CreateObject("Outlook.Application"
Set Itm = App.CreateItem(0)
With Itm
.Subject = ESubject
.To = SendTo
.Body = Ebody
'.Attachments.Add (NewFileName) ' Must be complete path
'.Display ' This property is used when you want
' the user to see email and manually send. Then
' comment out rest of code except “End With” statement
' and "End Sub" statement.
.send
End With
Set App = Nothing
Set Itm = Nothing
End Sub
