sspatriots
Well-known Member
- Joined
- Nov 22, 2011
- Messages
- 585
- Office Version
- 365
- Platform
- Windows
I've found code on line that could probably do what I need this one to do, but involved quite a bit of change to this code. Looking to see if there is a more simple solution. The code below sends out an auto generated e-mail to the e-mail addresses down a column in my spreadsheet. However, when it is sent the recipient sees my name as the sender. Is there a rather simple way to make it so that the recipient sees "NoReply@domain.com" as the sender and not myself? Thanks in advance, SS
VBA Code:
Sub SndEMail()
' Works in Excel 2000, Excel 2002, Excel 2003, Excel 2007, Excel 2010, Outlook 2000, Outlook 2002, Outlook 2003, Outlook 2007, and Outlook 2010.
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range
Dim sign As String
Dim HTMLBody As String
Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
On Error GoTo cleanup
For Each cell In Columns("AL").Cells.SpecialCells(xlCellTypeConstants)
If cell.Value Like "?*@?*.?*" And _
LCase(Cells(cell.Row, "AO").Value) = "yes" Then
Set OutMail = OutApp.CreateItem(0)
Dim FullName As String
Dim FirstName As String
Dim LastName As String
Dim SpacePos As Integer
FullName = Cells(cell.Row, "AJ").Value
SpacePos = InStr(FullName, " ")
FirstName = Left(FullName, SpacePos - 1)
LastName = Right(FullName, Len(FullName) - Len(FirstName))
On Error Resume Next
With OutMail
.To = Cells(cell.Row, "AL").Value 'cell.Value
.Subject = Cells(cell.Row, "B").Value
.HTMLBody = "<BODY style=font-size:11pt;font-family:Times New Roman>" & FirstName & _
"," & _
"<BR/><BR/>As a courtesy, this email is to provide you with a follow up on the production status of the job in the subject line. " & _
"The current estimated completion date is " & _
Cells(cell.Row, "L").Value & _
"." & _
"<BR/><BR/>If this date is too soon, please advise asap so that we can move the job back in the production schedule based on your requirements." & _
"If you are unable to accept all material within 30 days from the completion date, you must coordinate a storage facility for us to ship the materials to. " & _
"<BR/><BR/><BR/><BR/>Thank you," & _
"<BR/><BR/>John Doe" & _
"<BR/>Engineering" & _
"<BR/>Some Company" & _
"<BR/>JDoe@domain.com" & _
"<BR/>800-123-4567, x9999" & HTMLBody & "</BODY>" & sign
'You can also add files like this:
'.Attachments.Add ("C:\test.txt")
.Send 'Or use Display.
'.Display
End With
On Error GoTo 0
Set OutMail = Nothing
End If
Next cell
cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub
