VBA Email Format Problem

NullaDave · Sep 16th 2016

Hi All,

I am working on a document that takes data from A userform field, converts it to HTML and sends the information in an email. I've got everything working really well using lots of help from this website and Ron deBruin's Email code.

The only niggly problem I have is if a particular field in the userform is left blank, then the email generated still has a blank line and I really want the line to be deleted and the lines below moved up one. An example is here:

Customer
Harry's Diner

Address
25 Any Street
<blank line>
Any Town
<blank line>
West Springfield
AN3 0TH

What I really want is:

Customer
Harry's Diner
Address
25 Any Street
Any Town
West Springfield
AN3 0TH

I've tried putting in a few IF statements to try and get round it but it keeps putting the blank line in because the only code I know how get a blank character is "". This obviously treats the line as blank but still a separate line. Here is the code I have so far.

Code

StrBody = "<font size=""11px"" face=""Segoe UI"" color=""black"">" & "<br>" & _
        "Dear " & frmCalc.txtSP1.Value & SP2 & SP3 & "," & "<br><br>" & _
        "    Some text " & frmCalc.DTPDate.Value & "." & "<br><br>" & _
        'this is the beginning of the address code
        "<b>Customer:</b><br>" & frmCalc.txtCust.Value & "<br><br>" & _   'Customer in Bold & Customer Name from Form
        "<b>Address:</b><br>" & _   'Address in Bold
        txtAdd1.Value & "<br>" & _  'Address Line 1 from Form
        txtAdd2.Value & "<br>" & _  'Address Line 2 from Form
        txtCity.Value & "<br>" & _  'Address Line 3 from Form
        txtCounty.Value & "<br>" & _  'Address Line 4 from Form
        frmCalc.txtPC1.Value & " " & frmCalc.txtPC2.Value & "<br>"  'Post Code from form

Display More

I can provide some more supporting code but I'm fairly sure this is where the changes need to be made.

Can anyone suggest a possible solution? This would sort out the same issue I have with a couple of other project I have as well so any helpful code would be most appreciated.

Many Thanks as always
Dave

**cytop** · Sep 17th 2016

Re: VBA Email Format Problem

Add a small function to a standard code module

Code

Public Function AddressBlock(pArray As Variant, Optional strJoin As String = "<br>") As String
   
   Dim iTemp As Integer
   Dim strReturn As String
   
   For iTemp = LBound(pArray) To UBound(pArray)
   
      If pArray(iTemp) <> vbNullString Then
         strReturn = strReturn & pArray(iTemp) & strJoin
      End If
   Next
   
   AddressBlock = strReturn


End Function

Display More

Then call it passing all the elements of the address in an array

Code

"<b>Address:</b><br>" & _ 'Address in Bold
AddressBlock(Array(txtAdd1.Value, txtAdd2.Value, txtCity.Value, txtCounty.Value))

NullaDave · Sep 19th 2016

Re: VBA Email Format Problem

That has worked perfectly!

Thanks cytop.

VBA Email Format Problem

Participate now!

Share