VBA Macro Conditional letter generation

Hi,

I am using Excel VB Editor to create a macro that can be used to generate different letters onto a word document. This part is fine.

I need to be able to generate a different letter based on the input from the user.

For example, the text in the letter will be different if there has been contact with the customer as opposed to no contact with the customer.

It's like an if statement - How do I insert this into the code...I've attached my code below and your help would be greatly appreciated..

[VBA]
Private Sub cmdGenerate1_Click()
With UserForm
strDate1 = txtDate1.Text
strSalutation1 = cboSalutation1.Text
strFname1 = txtFName1.Text
strSurname1 = txtSurname1.Text
strAddress11 = txtAddress11.Text
strAddress12 = txtAddress12.Text
strRefNo1 = txtRefNo1.Text
strPhoneContact1 = cboPhoneContact1.Text
strContactedOn1 = txtContactedOn1.Text
strFrom1 = txtFrom1.Text
strTo1 = txtTo1.Text
strFrom2 = txtFrom2.Text
strTo2 = txtTo2.Text
strFrom3 = txtFrom3.Text
strTo3 = txtTo3.Text
strFrom4 = txtFrom4.Text
strTo4 = txtTo4.Text
strCaseNumber = txtCaseNumber.Text
End With
Set appWord = CreateObject("Word.Application")
'Makes Word visible, adds a new document and
'sets the margins & font name/size
With appWord
.Visible = True
.WindowState = 1 'Maximised
.Documents.Add
.ActiveWindow.ActivePane.View.ShowAll = False 'turn off non-printing characters
.ActiveDocument.PageSetup.LeftMargin = "1"
.ActiveDocument.PageSetup.RightMargin = "1"
.Selection.WholeStory
With .Selection.Font
.Name = "Courier New"
.Size = "10"
End With
'Set up the outline of the text doc
.Selection.insertafter UserForm.cboSalutation1 & " " & UserForm.txtFName1 & " " & UserForm.txtSurname1 & Chr(13)
.Selection.insertafter UserForm.txtAddress11 & Chr(13) & UserForm.txtAddress12 & Chr(13)
.Selection.insertafter Chr(13)
.Selection.insertafter strDate1 & Chr(13) & Chr(13)
.Selection.insertafter "Dear " & strFname1 & "," & Chr(13) & Chr(13)
.Selection.insertafter "CHANGES TO YOUR RECORD" & Chr(13) & Chr(13)
.Selection.insertafter "As we discussed with you on " & strContactedOn1 & ", we are writing to advise"
.Selection.insertafter " you of changes to income information in your record for "
.Selection.insertafter strFrom1 & " to " & strTo1 & " in case number " & strCaseNumber

Set appWord = Nothing
End With
Range("A1").Select
End Sub
Private Sub MultiPage1_Change()
End Sub
Private Sub txtRefNo1_Change()
If txtRefNo1 = vbNullString Then Exit Sub
If Not IsNumeric(txtRefNo1) Then
MsgBox "Only numeric values accepted"
txtRefNo1 = vbNullString
End If
End Sub
Private Sub txtTo1_Change()
End Sub
Private Sub UserForm_Initialize()
Application.Visible = True
txtFName1.Value = ""
txtSurname1.Value = ""
txtAddress11.Value = ""
txtAddress12.Value = ""
txtRefNo1.Value = ""
txtContactedOn1.Value = ""
With cboSalutation1
.AddItem "Mr"
.AddItem "Mrs"
.AddItem "Ms"
End With
cboSalutation1.Value = ""

With cboPhoneContact1
.AddItem "Y"
.AddItem "N"
End With
cboPhoneContact1.Value = ""
Me.txtDate1.Text = Format(Date, "Medium Date")
End Sub
[/VBA]

Ok, I am going to ask. Why are you doing this from excel? This can be accomplished easily from Word using templates.

Hi Lucas,

I saw some of your posts before and just loved your signature (I hope you don't mind that I've copied the quote onto my signature in outlook!)

Anyway,thank you for responding.

If there is a better way, please tell me how...I've only done a simpler macro before using VBA in Excel...so naturally, i went down the same path when faced with this new project...

On the signature, I borrowed it from Abe. Feel free to use it. I've always found it to be true in my experiences.

could you upload your excel file so I can steal the userform instead of recreating it.

click post reply at the bottom left of the last post. Then scroll down and look for "manage attachments"

If it is an excel 2007 please save it as 2003 as I don't have 2007 at present.

Thank U! And thank you for the speedy reply! I've been hoping someone would reply all morning!!

I've attached the file - once you see it i can then explain it...

Lucas, I'm taking it for granted that you've had a look at my code (if i can call it that!) so I thought I'd fill you in on the details.

I didn't know how to input the following into the letter:
Phone Contact - Y/N
Y - generates text about conversation
N - requests contact ASAP

In the "As we discussed with you..." sentence, if there is more than one period in question, how do I factor this in the letter?

Similarly, if there is more than one child - two, three, four or five..how do i cater for this in the letter?

Thank you so much for taking the time to look into this for me...

Jaffa, it's late here and I have a few other things I must do. I will definatley look at this tommorrow for you and get back with some ideas. Hope that is acceptable.

Hey Lucas,

Ofcourse it's ok. You are doing me a favour!

I'll keep looking at it and I'll look forward to your reply tomorrow.

Have a good night.

Good morning Jaffa, I moved your userform to a word file and simplified it a bit. I really only addressed the two different types of letters in this edition.

I had to zip it up as the forum will not allow me to upload .dot or template files. If you are not familiar with templates such as this you should just download it to your hard drive and double click on it.

The file that you will see will be a clone of the template file. In other words the template itself will not be affected by changes you make to the currently open document and the template can be used over and over. The userform is available because it is in the template but will not accompany the newly created document. The userform is activated by a document new procedure in the thisdocument module of the template file.

As I said, I only addressed the ability to select which document you choose by changing the phone contact drop down and selecting Y or N.

I did not create the other letter for you I just added some X's to this line in the N version in this sentence so you will have to work that part out.

As XXXXXXwe discussed with you on

the version above will be inserted if you choose N and the version without the X's will be inserted if you choose Y.

If this looks like a workabable direction for your project let me know and I will move your thread to the Word help forum.

Hi Lucas!

Thank you so much! You are a saviour! This is a definitely workable direction, albeit new to me...but I'll play around with it.

I followed your lead with the code and made changes and saved it, but it didn't change the template...how do I go about this??

Again, thank you so much!!!

Lucas,

scratch the second line of my previous post....was opening the wrong file!

I may have more questions later re this as word vba is new to me..would that be ok?

Thanks again!

to edit the template just open word and then go to file-open and select the .dot file. You can then edit it directly.

Do you want this moved to the Word help forum? It would help us get more input with your questions. Absolutley ask all the questions you want. I may not be the one that has the answers and that's why this forum is such a good resource.