+ Reply to Thread
Page 2 of 2 FirstFirst 1 2
Results 16 to 25 of 25

How to add input box for require record to mail?

  1. #16
    Forum Expert
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    1,936

    Re: How to add input box for require record to mail?

    I will ask again:
    1. What is the QueryString after applying the filter?
    2. Since a properly-implemented filter will automatically skip records with 'Sent' in the 'Remark' column and '-' in the 'Email ID' column, why do you need the inputboxes as well?
    Until you answer both questions, I'm not going to waste any more time on this.
    Cheers,
    Paul Edstein
    [MS MVP - Word]

  2. #17
    Forum Contributor
    Join Date
    06-29-2014
    Location
    India
    MS-Off Ver
    2010 Windows 10 64 bits
    Posts
    1,269

    Re: How to add input box for require record to mail?

    Hi Paul,

    Greetings from me!

    Glad to inform you that I have successfully amended the qry and getting correct output as per requirement.

    Now the remark and message count problem is to solve.
    After sending mail change remark 'Send' to 'Sent' in column 'V'
    At the end message how many mails sent.
    Input box for if the user wants to mail/create letter for the particular record only for duplicate receipt.

    Doubt: There are about 5000 records in the database. If we didn’t create a input box to mail particular records then ‘does it will take a long time to scan for entire sheet and send the mail or not?
    I have also create a macro for letters using the same code. If I want to create a particular record second time if there is a request of duplicate receipt then I have to delete the remark ‘Sent’ and type ‘Send’ again to create that particular record.

    Please Login or Register  to view this content.
    Thank you for your kind guidance to teach me mail merge.

    Thanking you,
    Sincerely,

    mso3

  3. #18
    Forum Expert
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    1,936

    Re: How to add input box for require record to mail?

    The mailmerge will take about the same time to run regardless of whether you're using an input box to get the record range. Indeed, the time taken for you to input those details is likely to be far more than it would take the mailmerge for any additional processing without the input boxes. Even with input boxes, you won't be able to generate duplicates unless you also change the 'Sent' to 'Send'.

    If you want to send letters, you can't use the same code: you would either have to use another macro for that or modify the existing one so you can choose between sending letters and sending emails. You'd probably also need to use a different mailmerge main document.

    For code with the inputboxes, try:
    Please Login or Register  to view this content.
    For code without the inputboxes, try:
    Please Login or Register  to view this content.
    Note: I've simplified your Const strQry String. However, as SQL is not my forte, I can't guarantee it works correctly. Check the output.
    Last edited by macropod; 03-19-2017 at 06:15 PM.

  4. #19
    Forum Contributor
    Join Date
    06-29-2014
    Location
    India
    MS-Off Ver
    2010 Windows 10 64 bits
    Posts
    1,269

    Re: How to add input box for require record to mail?

    Hi Paul,

    Thank you very much. Now everything is covered in the code as per requirement. In the both code I am getting error as follows:
    Getting run-time error 5922 word was unable to open the database Source
    On the following line:
    Please Login or Register  to view this content.

    Yes, for letters I have another macro.

    Thank you and have a nice time.

  5. #20
    Forum Expert
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    1,936

    Re: How to add input box for require record to mail?

    That may be because there's an error in my edit of your Const strQry String. As I said, SQL is not my forte. Try the code with your Const strQry String from post #17.

  6. #21
    Forum Contributor
    Join Date
    06-29-2014
    Location
    India
    MS-Off Ver
    2010 Windows 10 64 bits
    Posts
    1,269

    Re: How to add input box for require record to mail?

    Hi Paul,

    Yes, the error was in qry. After changing the qry statement the error has gone.

    Now a error 1004:
    run-time error 1004 application-defined or object-defined error on the following line ...
    Please Login or Register  to view this content.
    Thanking you,

  7. #22
    Forum Expert
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    1,936

    Re: How to add input box for require record to mail?

    Oops - typos in the With ThisWorkbook.Sheets("Data") ... End With block. I've edited that part of the code in post #18. Try the updated With ThisWorkbook.Sheets("Data") ... End With block.

  8. #23
    Forum Contributor
    Join Date
    06-29-2014
    Location
    India
    MS-Off Ver
    2010 Windows 10 64 bits
    Posts
    1,269

    Re: How to add input box for require record to mail?

    Hi Paul,

    Greetings from me!

    Excellent! Now it’s working fine as per requirement.

    I have also created a macro for letters. I amended the email code and achieve the target as per requirement successfully. Only the remark ‘Send’ to ‘Sent’ and letters count message at the end is not as per requirement. I couldn’t amend it. Everything is perfect. I’m marking the thread solved now but do suggest me an amendment in the letter code positively remark and letter count problem. I have created a new letter and one more column for letter remark namely ‘REMARK L’ column ‘W’.

    After a long time finally I learned mail merge, sql statement, filter with your kind guidance and sincere efforts to teach me positively. I’m thank you for the same and appreciate you by heart.

    Please Login or Register  to view this content.
    Thank you and have a nice time.

  9. #24
    Forum Expert
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    1,936

    Re: How to add input box for require record to mail?

    Quote Originally Posted by mso3 View Post
    I have also created a macro for letters. I amended the email code and achieve the target as per requirement successfully. Only the remark ‘Send’ to ‘Sent’ and letters count message at the end is not as per requirement. I couldn’t amend it. Everything is perfect. I’m marking the thread solved now but do suggest me an amendment in the letter code positively remark and letter count problem. I have created a new letter and one more column for letter remark namely ‘REMARK L’ column ‘W’.
    In that case:
    • change the With ThisWorkbook.Sheets("Data") ... End With block for the MergeToLetterWithPrompt sub to:
    Please Login or Register  to view this content.
    • change the With ThisWorkbook.Sheets("Data") ... End With block for the MergeToLetterNoPrompt sub to:
    Please Login or Register  to view this content.

  10. #25
    Forum Contributor
    Join Date
    06-29-2014
    Location
    India
    MS-Off Ver
    2010 Windows 10 64 bits
    Posts
    1,269

    Re: How to add input box for require record to mail?

    Hi Paul,

    Perfect! In this thread I learned a lot; 4 types of mail merge, filter, count message and remark. Credit goes to you for your kind guidance to teach me it patiently.

    I by heart appreciate you for the same.

    Thank you and have a nice time.

+ Reply to Thread
Page 2 of 2 FirstFirst 1 2

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1