Need e-mail to be from "NoReply@domain.com"

sspatriots

Well-known Member
Joined
Nov 22, 2011
Messages
547
Office Version
  1. 365
Platform
  1. 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
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Hi. I believe the email account would need to exist. Once created, you could use .SentOnBehalfOfName = "noreply@domain.com" and it will show that email as the sender.
 
Upvote 0
Solution
Hi. I believe the email account would need to exist. Once created, you could use .SentOnBehalfOfName = "noreply@domain.com" and it will show that email as the sender.
It will but the user will still see the original email account.
What the OP is asking is how to use Outlook to spoof an email address that you don't have an account set up for. Outlook does not want to do that.
email on behalf.jpg
 
Upvote 0
It will but the user will still see the original email account.
What the OP is asking is how to use Outlook to spoof an email address that you don't have an account set up for. Outlook does not want to do that.
View attachment 76711
It would need to be an outlook account where you are a member. Then it won't even indicate that you are sending on behalf as the sender. It will just indicate that account's name. Maybe it's a special group mailbox in office pro, but that's how I have it working
 
Upvote 0
Yes, look into '.SentOnBehalfOfName' that should change what is seen as the 'from' address.
 
Upvote 0
Hi. I believe the email account would need to exist. Once created, you could use .SentOnBehalfOfName = "noreply@domain.com" and it will show that email as the sender.
Account is set up. This worked great. Thank you. We aren't trying to spoof anyone as suggested in another post, this e-mail only lets our customer's know the status of their jobs and we don't want 500 e-mail replies coming back to the person responsible for sending this out who is not responsible for each job. Thank
 
Last edited by a moderator:
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top