Consulting

Results 1 to 12 of 12

Thread: VBA Macro Conditional letter generation

  1. #1
    VBAX Regular
    Joined
    Mar 2009
    Posts
    7
    Location

    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]

  2. #2
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    Ok, I am going to ask. Why are you doing this from excel? This can be accomplished easily from Word using templates.
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  3. #3
    VBAX Regular
    Joined
    Mar 2009
    Posts
    7
    Location
    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...

  4. #4
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    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.
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  5. #5
    VBAX Regular
    Joined
    Mar 2009
    Posts
    7
    Location
    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...

  6. #6
    VBAX Regular
    Joined
    Mar 2009
    Posts
    7
    Location
    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...

  7. #7
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    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.
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  8. #8
    VBAX Regular
    Joined
    Mar 2009
    Posts
    7
    Location
    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.

  9. #9
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    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.
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  10. #10
    VBAX Regular
    Joined
    Mar 2009
    Posts
    7
    Location
    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!!!

  11. #11
    VBAX Regular
    Joined
    Mar 2009
    Posts
    7
    Location
    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!

  12. #12
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    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.
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •