text formatting in email (another stupid question) 1

[mirgss]

Okay, so I finally have my code set so it pulls the data from a table and it's all nice and pretty....but I have a few questions.

1. Can I force a certain font type in this email? I have a coworker who likes to use green comic sans (no joke) and we don't want that going out to our customers.

2. Can I bold the variable "workOrder" in the body of the email?

3. Can I make it so the "Building", "Floor" and "Pillar" only appear if those fields are filled in? So I don't have something that looks like this:

Building:
Floor:
Pillar:

Thanks again for everything...my little brain feels so full!

Code:
Private Sub sendResponse_Click()
Dim email$, ref$, notes$, strBody$, strBody2$
Dim workOrder As Long
Dim Building$, custServ$, strCC$, strWhere$
Dim objOutlook As Object
Dim objEmail As Object

custServ = "DTMB-customerservice@michigan.gov"
strWhere = "Building = '" & Me.Building & "'"
strCC = Nz(DLookup("emailCC", "tblBuilding", strWhere), "")

UserName = Me!UserName & "@michigan.gov"
ref = Me!ref
notes = Me!notes
 If Me!workOrder = notNull Then
    workOrder = Me!workOrder
   '  Else
       '  workOrder = "N/A"
 End If
 If Me!Building = notNull Then
    Building = Me!Building
 '   Else
    '    Building = "N/A"
 End If
 If Me!Pillar = notNull Then
    Pillar = Me!Pillar
End If
If Me!Floor = notNull Then
    Floor = Me!Floor
End If
                     
Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(outlookMailItem)
strBody = "Thank you for contacting DTMB Customer Service Center. Work order number " & workOrder & " has been created for your most recent request:" & vbNewLine & vbNewLine & "Request Details: " & notes _
& vbNewLine & "Building: " & Building & vbNewLine & "Floor: " & Floor & vbNewLine & "Pillar: " & Pillar
strBody2 = "If you have questions relating to this request, please reference the work order number when you contact the DTMB Customer Service Center at 517.373.6227 or DTMB-CustomerService@michigan.gov." & vbNewLine & vbNewLine & "Thank you!"

With objEmail
    .To = UserName
    .CC = strCC
    .SentOnBehalfOfName = "DTMB-customerservice@michigan.gov"
    .Subject = "Customer Service Center Follow Up"
    .Body = strBody & vbNewLine & vbNewLine & strBody2
                            
        For Each objOutlookRecip In .Recipients
                 objOutlookRecip.Resolve
        Next
        
    .Display 'sends the email in Outlook.  Change to DISPLAY if you want to be able to
      'modify or see what you have created before sending the email
End With

End Sub

 

[PHV]

3) Replace such things:
If Me!workOrder = notNull Then
with something like this:
If Not IsNull(Me!workOrder) Then

1) and 2) I'm afraid you have to play with .HTMLBody instead of .Body

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Andrzejek]

I would also suggest to set one way of declaring variables and stick with it:
[tt]
Dim email$, ref$, notes$, strBody$, strBody2$
Dim workOrder As Long
[/tt]
email$ is the same 'As String' but not many people remember this way from (who knows how many) years ago. And if somebody else (or YOU in a few years) will maintain this code, they may go: "What the ... is this?"

I would bite the bullet and go with:
[tt]
Dim email As String [green]'This or that[/green]
Dim ref As String
Dim notes As String [green]'Notes from somewhere[/green]
Dim strBody As String
Dim strBody2 As String
[/tt]
You can add comments on each line.

But that could be just me....

Have fun.

---- Andy

 

[mirgss]

Okay, I've changed my use of = to Is NotNull. But in the email body I only want "Building:" "Floor: " etc to appear if the respective fields are notNull. So if I don't have a pillar number, my email can appear like this:

Thank you for contacting DTMB Customer Service Center. Work order number blahblahblah has been created for your most recent request:

Building: Hannah
[since no floor or pillar number has been entered, nothing will appear here]

If you have questions relating to this request....etc etc etc.

Does that make sense?

 

[Andrzejek]

How about:

strBody = "Thank you for contacting DTMB Customer Service Center. " 

If Len(workOrder) > 0 Then
    strBody = strBody & "Work order number " & workOrder & " has been created for your most recent request:" & vbNewLine & vbNewLine 
End If

If Len(notes) > 0 Then
    strBody = strBody & "Request Details: " & notes 
End If
...

Well, you get the idea, right?

Have fun.

---- Andy

 

[mirgss]

Oooooooh! I'll give that a shot. Thanks again! This will solve my problem of sending a separate email if there is no work order number too. You are a rockstar!

Do you know of any particularly good resources about using .HTMLBody or will I come up with a reasonable result using Google?

Miranda

 

[Andrzejek]

You are welcome.

I do a lot of 'building on-the-fly' either SQLs or strings like the one I showed you.

I did not play with HTMLBody, but it may be just adding the tags to make font <b>bold</b> or <i>italic</i>, but that's just a guess.

Have fun.

---- Andy

 

[Andrzejek]

Without knowing much about HTML I did this:

With objEmail
    ....
    .HTMLBody = "<p>This is <span style='color:red'>my try</span></p>"
    .HTMLBody = .HTMLBody & "<p>of sending <b>HTMLBody</b></p>"
    .HTMLBody = .HTMLBody & "<p>In the e-mail.</p>"
    ....
End With

And I got a message that looked something like:
[tt]
This is [red]my try[/red]
of sending HTMLBody
In the e-mail.
[/tt]
I typed this text in Word (2010), colored it, made it bold, then saved it as HTML document, opened in Notepad and looked at the code generated. Pssst... don't tell anybody.

Have fun.

---- Andy

 

[mirgss]

Thanks again...you are my savior! I guess I just need to experiment a little more

 

[Andrzejek]

You are welcome.

I accept cash, personal checks, and major credit cards...

Have fun.

---- Andy