Link to home
Create AccountLog in
Avatar of f19l

asked on

What is the VB code to show that an email is sent from a particular distribution group?


I currently have some VB code that emails a spreadsheet to a list of people. I have now been told that the email must be sent from a newly set-up distribution list. I thought it would be easy to change the code myself but unfortunately I could not do it. I attach the code below so that you can see what I have.

Sub Email()

Application.DisplayAlerts = False

Dim myFile, myDir, myDate As String
Dim myApp, myMail As Object

myDate = Format(Range("Date"), "d-Mmm-yyyy")
myFile = ActiveWorkbook.Name
myDir = "L:\Finance\"

'Send email to Risk

        Set myApp = CreateObject("Outlook.Application")
        Set myMail = myApp.CreateItem(0)
        On Error Resume Next
        With myMail
            .To = "distribution"
            .CC = ""
            .BCC = ""
            .Subject = "PL " & myDate
            .Body = "PL for " & myDate
            .Attachments.Add myDir & "PL " & myDate & ".xlsx"
            .Attachments.Add ""
            .Attachments.Add ""
        End With
        On Error GoTo 0
        Set myMail = Nothing
        Set myApp = Nothing

End Sub

Open in new window

Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

.To = "distribution"

is the "alias" of where the email is send to.
you may open up the outlook on the pc on which this code runs, and check out the alias/contact for that name
Avatar of f19l


The ".To = distribution" is just something that I added as the actual code in my spreadsheet shows a list of ten names and I did not want to put all of them in my question. I did not want to clutter up the code needlessly.
ok, let's clarify, because I actually see some eventual issue in "terms".

>must be sent from a newly set-up distribution list.
taken as written, you want the "from" to be specified?
I don't think this can be done via VB code (but I might be wrong).

otherwise, what is the issue?
any error message you get?
Avatar of f19l


Yes. Currently when I run the VB code the email appears but now it has a new box called "From" into which I now have to manually type the distribution group that I am now sending the emailing from. I would like this to be fully automated and included in the VB code, if possible.
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account