From field OUTLOOK VBA

Posted on 2011-04-26
Last Modified: 2012-05-11
I need to send an email using vba on behalf of another mailbox. How can I tweak the code below to accomplish this?

Public Sub CreateAnEmail_Corp(filename As String, FullName As String, RecipentEmail As String, SupervisorEmail As String)
On Error Resume Next
Dim db As Database
Dim rs As Recordset
Dim ClientEmail As String
Dim DisplayMsg As Boolean
Dim AttachmentPath As String
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim objOutlookExplorers As Outlook.Explorers
Dim myarray() As String
Dim myaddresses() As String
Dim x As Integer
Dim fs As Object
Dim BuiltPath As String
Dim response As Integer
Dim WasOpen As Boolean

DisplayMsg = True
'AttachmentPath = "J:\data\" & ReportCaption & ".pdf"

If EnoughPrompts = 0 Then
MsgBox "The email is about to be created!" & vbCrLf & _
        "If nothing appears to be happening, the Outlook security box may be hiding behind an open window." & vbCrLf & _
        "Click the Outlook icon on the taskbar to bring it to the front, if necessary."
End If
EnoughPrompts = 1

Set objOutlook = GetObject(, "Outlook.Application")
'MsgBox Err.Number & " " & Err.Description
If Err.Number = 429 Then
    WasOpen = False
    ' Create the Outlook session.
    Set objOutlook = CreateObject("Outlook.Application")
    WasOpen = True
End If
    Dim ns As Outlook.NameSpace
    Dim Folder As Outlook.MAPIFolder
    Set ns = objOutlook.GetNamespace("MAPI")
    Set Folder = ns.GetDefaultFolder(olFolderInbox)
    Set objOutlookExplorers = objOutlook.Explorers
If WasOpen = False Then
    objOutlook.Explorers.Add Folder
    'done opening
End If

AppActivate "Microsoft Outlook"

' Create the message.
'Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

With objOutlookMsg
    'can't do this, the mail never gets sent
    ' Add the from recipient(s) to the message.
    'Set objOutlookRecip = .Recipients.Add(somebody)
    'objOutlookRecip.Type = olOriginator
    ' Add the CC recipient(s) to the message.
    Set objOutlookRecip = .Recipients.Add(RecipentEmail)
    objOutlookRecip.Type = olto
    ' Add the CC recipient(s) to the message.
    Set objOutlookRecip = .Recipients.Add(SupervisorEmail)
    objOutlookRecip.Type = olCC

   ' Add the BCC recipient(s) to the message.
    Set objOutlookRecip = .Recipients.Add("Kelly")      objOutlookRecip.Type = olBCC
   ' Set the Subject, Body, and Importance of the message.
   .Subject = "Notice of File"

    .BodyFormat = olFormatHTML
   .HTMLBody = "<p>" & FullName & "," & "</P>"
   .HTMLBody = .HTMLBody & "The attached Notice to File is being placed in your Personnel File for failure to complete your required training by the due date. <br><br>"
   .HTMLBody = .HTMLBody & "Please <a href = ''>CLICK HERE</a> to take your courses immediately.   This training is mandatory and critical to ensure you are aware of important Company policies and procedures. <br><br>"
   .HTMLBody = .HTMLBody & "In the future, failure to complete your Compliance training by the due date may lead to disciplinary action, up to and including termination. <br><br>"
   .HTMLBody = .HTMLBody & "Thank you,<br><br>"
   .HTMLBody = .HTMLBody & "Training Team"
   ' Add attachments to the message.
   'reportcaption is passed in and split into myarray
   'one attachment needs to be added for each element in myarray

        AttachmentPath = "J:\data\" & filename
    If Not IsMissing(AttachmentPath) Then
        Set objOutlookAttach = .Attachments.Add(AttachmentPath)
    End If

   ' Resolve each Recipient's name.
   For Each objOutlookRecip In .Recipients

   ' Should we display the message before sending?
   If DisplayMsg Then
   End If
End With

Set objOutlook = Nothing

End Sub

Open in new window

Question by:kwarden13
    LVL 19

    Accepted Solution

    Here is the code you need to have before you start with your email.  Many of these objects you have already set so you need to decide how you want to do this;

    Basically at the very least you can put this right before you start with objOutlookMsg. needs to be the email address of the shared email.

    I have tried to follow your namings but may have missed something.

    Set objOutlookRecip = ns.CreateRecipient(
    objOutlookRecip.Resolve   'this will show the outlook security message
    If objOutlookRecip Is Nothing Then
        msgbox "Problem"
    End If
    Set Folder = ns.GetSharedDefaultFolder(objOutlookRecip, olFolderInbox)
    If objFolder Is Nothing Then
        Msgbox "Problem"
    End If
    Set objOutlookMsg = Folder.Items.Add

    Open in new window


    Author Comment

    It didn't work
    LVL 19

    Expert Comment

    what did not work?

    Assisted Solution

    Its ok I got it to work with just adding 1 line

     objOutlookMsg.SentOnBehalfOfName = "Email Address"
    Thanks though

    Author Closing Comment

    My solution works best.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Having just graduated from college and entered the workforce, I don’t find myself always using the tools and programs I grew accustomed to over the past four years. However, there is one program I continually find myself reverting back to…R.   So …
    Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
    The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now