formating and sending emails in access with vba (1 Viewer)

VBANEWBIE

Registered User.
Local time
Yesterday, 19:33
Joined
Oct 17, 2016
Messages
17
Hello,

I'm running:

access 2016
outlook 2016


having a few issues...

1) need to be able to state certain font and size for email
2) need to be able to make certain words in the body bold
3) email need to be Send From a different email address, not mine



the code below works,
however, no matter what I try, I cant seem to tackle the three items above...

any help would be greatly appreciated...

thanks

The Code:

Code:
 Private Sub Send_eMail_McKay_Guar_1_Click()
 Dim MyDb As DAO.Database
Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim strEmail As String
Dim strMsg As String
Dim oLook As Object
Dim oMail As Object
 Set MyDb = CurrentDb
Set qdf = MyDb.QueryDefs("qrySHLMG1")
 For Each prm In qdf.Parameters
    prm.Value = Eval(prm.Name)
Next
 Set rs = CurrentDb.OpenRecordset("SELECT GlobalID, RptName, Deadline, pl_LegalPlanName, Salutation2, AdminNameForward FROM qrySHLMG1")
 Set oLook = CreateObject("Outlook.Application")
With rs
        .MoveFirst
        Do Until rs.EOF
        myRecipient = .Fields(0)
            If IsNull(myRecipient) = False Then
                Set oLook = CreateObject("Outlook.Application")
                Set oMail = oLook.CreateItem(0)
                With oMail
                    .To = "
                    .cc = ""
                    .bcc = ""
                    .body = "Hello " & rs!Salutation2 & vbCrLf & vbCrLf & "Attached is the required annual notice for the " & rs!pl_LegalPlanName & ".  " & "Please open the attachment and follow the instructions on the cover letter.  " & "The safe harbor notice should be distributed to participants no later than " & rs!Deadline & ".  " & vbCrLf & vbCrLf & "If you have any questions, please do not hesitate to contact" & rs!AdminNameForward & " or me anytime.  " & vbCrLf & vbCrLf & "Best regards, " 
                     .Importance = 2  'Importance Level  0=Low,1=Normal,2=High
                    .Subject = "Required Annual Notice for the " & rs!pl_LegalPlanName
                    .Attachments.Add ("C:\Users\me\Documents\RPTTESTEXPORT\test.pdf")
                    .Send
                End With
            End If
                    .MoveNext
        Loop
End With
Set oMail = Nothing
Set oLook = Nothing
 End Sub
 

Gasman

Enthusiastic Amateur
Local time
Today, 00:33
Joined
Sep 21, 2011
Messages
7,114
I'll have a punt.

For 1 and 2 look at .RTFfBody or .HTMLBody of the mail item.
For 3. Look at .SendUsingAccount property.

RTF might be best, I've had lots os problems trying to format HTML emails :)

Someone only recently asked the same question for 3, so search on SendUsingAccount

HTH
 

Minty

AWF VIP
Local time
Today, 00:33
Joined
Jul 26, 2013
Messages
7,505
You will need to format the email as html and then use html codes to format the text accordingly, so something like (not complete code, but you'll get the idea);
Code:
 Variable_Body = "<style> " & _
                    "p {font-size:11pt; font-family:Calibri}" & _
                    "</style>" & _
                    "<p>" & "Good " & Greeting & sContact & "," & "</p>" & _
                    "<p>" & "" & "</p>" & _
                    "<p>" & "Please find attached your " & Variable_Subject & " <br/>" & _
                    "<p>" & "" & "</p>" & _
                    "<p>" & "Please arrange for a PO to be sent over if you wish to proceed. <br/>"


 With OutMail
        .BodyFormat = 2     [COLOR="Green"]   'olFormatHTML = 2 but without the reference being needed![/COLOR]
        .To = Variable_To
        .CC = ""
        .BCC = ""
        .Subject = Variable_Subject
        .Attachments.Add (VarAttachFile)
        .HTMLBody = Variable_Body & signature
        .Display                             [COLOR="green"]  'or use .Send[/COLOR]
        .ReadReceiptRequested = False
        .SentOnBehalfOfName =  "AnAccount@yourco.com"

 End With
 

Users who are viewing this thread

Top Bottom