VBA Merge Code (1 Viewer)

[Samantha]

Hi All,

So I have a access database and I have delevolped a code (actually borrowed a code) to send all of the information to a MS Word template. My question is - not all of my contacts have an e-mail address so in the event that they do not the fax will be inserted instead. I have that part set but I want to add in the text "E-mail:" or "Fax:" depending on the circumstance. I am not sure about how to go about it.

Any help is greatly appreciated!
Samantha

Private Sub MergeBttn_Click()
'Declare variables for storing strings.
Dim AddyLineVar As String, SalutationVar As String
Dim DeliveryAdd As String

'Start building AddyLineVar, by dealing with blank last name fields.
If IsNull([sfrmContacts].[Form]![Last]) Then
AddyLineVar = [Company]
'Just set salutation to generic.
SalutationVar = "Sir or Madam"
Else
AddyLineVar = ([sfrmContacts].[Form]![Title]) & " " & ([sfrmContacts].[Form]![First]) & " " & ([sfrmContacts].[Form]![Last])

'Add Company on after name.
If Not IsNull([Company]) Then
AddyLineVar = AddyLineVar & vbCrLf & [Company]
End If

'Salutation will be customer's last name
SalutationVar = ([sfrmContacts].[Form]![Title]) & " " & ([sfrmContacts].[Form]![Last]) & ", "
End If

'Start building DeliveryAdd, by dealing with blank email fields.
If IsNull([sfrmContacts].[Form]!) Then
DeliveryAdd = [sfrmContacts].[Form]![BusinessFax]
End If

'Add line break and Address lines.
AddyLineVar = AddyLineVar & vbCrLf & ([sfrmContacts].[Form]![Address])
'Tack on line break then city, state, and zip.
AddyLineVar = AddyLineVar & vbCrLf & ([sfrmContacts].[Form]![City]) & ", "
AddyLineVar = AddyLineVar & ([sfrmContacts].[Form]![State]) & " " & ([sfrmContacts].[Form]![ZipCode])

'Declare an instance of MS Word.
Dim Wrd As New Word.Application
Set Wrd = CreateObject("Word.Application")

'Specify the path and name to the word document.
Dim MergeDoc As String
MergeDoc = Application.CurrentProject.Path
MergeDoc = MergeDoc & "\WordFormLetter.dotx"

'Open the word document template, make it visible.
Wrd.Documents.Add MergeDoc
Wrd.Visible = True

'Replace each bookmark with current data.
With Wrd.ActiveDocument.Bookmarks
.Item("ProjectDescription").Range.Text = ProjectDescription
.Item("AddressLines").Range.Text = AddyLineVar
.Item("Salutation").Range.Text = SalutationVar
.Item("Phone").Range.Text = sfrmContacts.Form!Phone
.Item("JobNumber").Range.Text = JobNumber
.Item("PMInitials").Range.Text = Manager
.Item("Typist").Range.Text = [Entered_By]
.Item("JobNumber2").Range.Text = JobNumber
.Item("Phone2").Range.Text = sfrmContacts.Form!Phone
.Item("BusinessFax2").Range.Text = sfrmContacts.Form![BusinessFax]
.Item("Delivery").Range.Text = DeliveryAdd
.Item("ProjectDescription2").Range.Text = ProjectDescription
End With

 

[jdraw]

What if they don't have a FAX?

You could query your tables and get counts of
Emails, FAx, neither

 

[Samantha]

All the contacts have one or another as of now, although it is a possiblity in the future. So planning for the future, it would be nice to get a message box of the error and open a modal form to correct it before proceeding. I did figure out the text of my original question, this is how I changed the code.

'Start building DeliveryAdd, by dealing with blank email fields.
If IsNull([sfrmContacts].[Form]!) Then
DeliveryAdd = "Fax: " & [sfrmContacts].[Form]![BusinessFax]
Else
DeliveryAdd = "E-mail: " & [sfrmContacts].[Form]![Email]
End If

Also now that I am thinking about it How can I get the code to move to the next line in the event that any of this data is missing?

With Wrd.ActiveDocument.Bookmarks
.Item("ProjectDescription").Range.Text = ProjectDescription
.Item("AddressLines").Range.Text = AddyLineVar
.Item("Salutation").Range.Text = SalutationVar
.Item("Phone").Range.Text = sfrmContacts.Form!Phone
.Item("JobNumber").Range.Text = JobNumber
.Item("PMInitials").Range.Text = Manager
.Item("Typist").Range.Text = [Entered_By]
.Item("JobNumber2").Range.Text = JobNumber
.Item("Phone2").Range.Text = sfrmContacts.Form!Phone
.Item("BusinessFax2").Range.Text = sfrmContacts.Form![BusinessFax]
.Item("Delivery").Range.Text = DeliveryAdd
.Item("ProjectDescription2").Range.Text = ProjectDescription
.Item("ProjectManager").Range.Text = PmName
.Item("PMTitle").Range.Text = [sfrmPMTitles].[Form]![Title]