Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Aug 2016
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL Code Does not run For VBA RunMerge ( ) by Macropod

    I've read just about all the threads on here and figure out why I have a "Object Variable or With Block Not Set" for the aforementioned VBA code
    to add multiple lines of information within a single email, i.e. "grouping."

    The error occurs in exactly the same location as another post,

    Within the Sub EmailMergeTableMaker(DocName As Document)

    on line: .Paragraphs(1).Range.Delete

    Saved the code in "Email Merge Main Document.docm" yes added the "m" to the code

    When I open the above Word document, the SQL code does not run. It could be a path issue in the code but I am not versed enough in VBA
    to know. Could be that I save the unzipped files on the desktop to a folder called EmailMerge? The other person save to their documents to the Documents folder.

    Many thanks in advance - I'm sure it's something staring me in the face but am so perplexed at moment I cannot see.

    Mort in Dallas

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    That suggests you haven't followed the instructions. Without a link to the 'other post' or access to your documents, I can't really comment further.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    New Lounger
    Join Date
    Aug 2016
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you Paul.

    Concur on the "other post" It was solved in that the person had answered NO to the SQL inquiry. Once she answered YES code worked. How might I reference since I'm very new here?

    I do not mean to be argumentative but to suggest I have not read your tutorial is most presumptive on your part.

    I am a 4 degreed Ph.D. scientist and am quite capable of reading the written word. I realize that some may not follow your instructions. I did!

    My observation and I could be wrong, but what I really think is that you are too close to your own program and cannot fathom what the novice see's. You need to put yourself in the shoes of the less experienced and understand we are not as fluent in this as you. See? I have to explain complex environmental laws to what
    we in the U.S. refer to as "Mom & Pops" and pride myself in being able to EFFECTIVELY communicate with them to where they understand what is going on. I also read law, day in, day out and make regulatory interpretations. Now that's complex!!

    There is something, most likely very minor, amiss here, and I cannot put my finger on it. I just need a lil assistance, other than "you didn't read the instructions"
    How about reiterating as if you had no clue what was going on, and put yourself in our shoes.

    Issue - please clarify:
    I note on page 25 of your instructions before the VBA code for EmailMerge you state, "...insert the following code into a normal VBA module in the above Catalog/Directory mailmerge main document, then..."

    Now, because this statement is within the topic "Merging by Catalog/Directory to E-Mail" should not you have meant to write "emailmerge main document
    vs mailmerge main document?" If you actually did mean "mailmerge main document" you should have clarified that. Why? Because to me it is intuitive one should use the "emailmerge" doc for are we not dealing with emails? Also why include two different "main documents?"

    There are 4 documents in your unzipped file (if incorrect, please correct me):
    1. Your tutorial
    2. Catalogue Merge Data.xls (are there spaces between words? Does it matter? Also .xls is the old format for Excel, should not we save that first to .xlsx? If not important so state)
    3. Email Merge Main Document.doc (again, are there spaces between words or does it make any difference?)
    and
    4. Mail Merge Main Document.doc (again, are there spaces between words or does it make any difference?)

    To me at least #4 deals with the first part of your tutorial - mail merge - correct or not?
    #3 deals with email merge, correct or not?

    Mail Merge <> Email Merge correct?

    I followed your steps ver batim, as least the way I interpret form the way you write, yet when I open the email merg doc the SQL does not run.

    Does it matter what folder one extracts the zipped files to - I simply named my EmailMerge - one word. I assume you can save in any folder, on the desktop?
    Does it have to be save within the Documents folder?

    I commend you on this complex routine, however, be considerate of those of us who want to learn.

    Is not your example Email Merge Main Document.doc meant to be used for this email merge exercise?
    Is not the VBA code to be saved in this document in a "normal" VBA module?
    When I opened the Email Merge Main Document, no SQL ran.

    I copied the VBA code into a module. When I saved, I noted that unlike Excel, no warning occurs that this is a macro enable document. Unlike Excel, does this mean for Word you can have macro's in a .doc document or
    must you first do a file Save As macro enabled.

    We all learn, we all progress for those who are patient enough to teach.
    And to you, Best Wishes Paul,
    Mort in Dallas, Texas USA

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Those who follow the instructions verbatim don't have the problems you say you're having.

    You can add links to other threads simply by copying their urls and pasting into your own post.

    You can attach a document to a post via the paperclip symbol on the 'Go Advanced' tab at the bottom of this screen.

    See: http://windowssecrets.com/forums/faq...eading_posting
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  5. #5
    New Lounger
    Join Date
    Aug 2016
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Paul. Especially for the link on editing and posting!
    Again, most of us are much less experienced in this than you.

    If you may have time to answer below I think? I can go forward?

    Obviously then I'm doing something wrong and it will not be the first time nor the last. We are human and subject to err, fully admit that.

    I do recall this, which may or may not be of consequence. When I first opened Email Merge Main Document, the SQL did run.
    I did not recognize any of the folders nor path, because there was no "pay attention to this in your instructions" I thought maybe something was amiss
    and answered NO. You may think about added a note in your tutorial about the SQL under Merging by Catalog/Directory to E-Mail?

    Now get this. Since I answered NO, no matter if I extract the files again and again, I cannot get the SQL to run.
    Maybe I need to delete all the files in the download, and download again?
    It is obvious I did something permanent - what is the question.

    Please simply concur or correct me if this sequence of events is wrong. I see this as a first step to resolution.
    1. Download and extract your zip files to a folder of one's choosing
    2. The extracted files are:
    PaulsFiles.jpg
    3. You were kind enough to give an example file to practice with - Email Merge Main Document.doc
    4. Open that document and click Developer > Visual Basic > Insert Module
    5. Copy the VBA code from your tutorial, from the Merging by Catalog/Directory to Email section into module
    6. Once code is copied, save - again, not clear to save as a doc or as a docm?
    7. At this point I'm not clear and here may be my problem - "...set up a separate Catalog/Directory mailmerge main document..."

    Previously I had presumed you had done that for us and it was one of the files downloaded, but now I think not. True?
    Not being fluent as you and others in this, I need a lil more help at this point.

    Please help me here and maybe it will help others as well.
    1. assume to set up Catalog/Directory mailmerge main document in a Word File - correct?
    2. I don't know the reason for the borders around <<City>> <<Representative>> and <<Sales>> might you please explain
    3. The chevrons imply these are merged fields but it is unclear to me, and possibly others, from which document are we
    getting this information - the Excel File or the Word file entitled "Email Data Source"? - Think I figured this out after re-reading
    4. The Excel file has the headers City, Representative, and Sales
    5. The Word file (EmailDataSource) - from reading, yes I do read - smile, is a product of the process and thus cannot be the source of data, has to be the Excel file - agree?
    6. We merge the fields City, Representative, and Sales from the Excel file into the word document to be called, I assume Catalog_DirectoryMaiMergeMainDocument. doc - correct?
    No clue from the code as to name of this new document

    Thanks again for your patience and understanding. Do realize while all the steps in here are most obvious to you, it is not so obvious to those who seek to use this process
    As you say Cheers,
    Mort in Dallas

  6. #6
    New Lounger
    Join Date
    Aug 2016
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Update Paul -
    Made a Word document and called it Catalog_DirectoryMailMerge_MainDocument
    used the fields City, Representative, and Sales from the Excel file to merge into the document via emailmerge in Word

    Realized the results of the RunMerge is the document called EmailDataSource.
    My results differed but I think I know why for I had extra returns....will do again.

    Suggestion: If someone else is having issues with the code, rather than firing back "read the instructions," which is rather callous, might I suggest
    responding with - insure you have generated a document, as discussed at the bottom of page 25 of my tutorial, and name it something like:
    Catalog_Direcotry_MailMerge_Main_Document. Be sure to insert the VBA code into this document.

    When opening this document if a pop-up appears stating some SQL code will run on opening the document, simply click Yes. No will abort the procedure from running.

    Will keep in touch....getting there...
    Mort in Dallas


    The results I obtained look nothing like yours so I'm still doing something wrong. Yes I commented out the email and uncommented the sendtoNewDocument.

    Bottom line - what am I trying to accomplish?

    I have an Excel (2013) file with a list of facilities with anywhere from 1 to 20 chemicals associated with each facility. There is additional info about the facility, ID#, address, city, state, etc.

    I need to send an individual email to each facility listing all the chemicals we have questions about.
    The concept is straight forward, achieving the results is something else...suggestions most, most welcomed.
    Mort in Dallas

  7. #7
    New Lounger
    Join Date
    Aug 2016
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Very close Paul....now some error I cannot fathom - looked in code, looked in Excel, looked in Word doc's?

    RecipientError.jpg

    Recipient = email
    Representative = Name
    Seems I need to change something.
    Have left your EmailMerge Doc exactly as is...suggestions?
    Thanks Mort

  8. #8
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    If you answer 'No' to the SQL prompt when you open a mailmerge main document, it won't connect to the data source. If you then save the document it ceases to be a mailmerge main document and you'd need to reconnect it to the data source before you can use it again. This is standard mailmerge stuff that I didn't think needed explaining in what is, after all, a tutorial covering some fairly advanced stuff.

    As for:
    7. At this point I'm not clear and here may be my problem - "...set up a separate Catalog/Directory mailmerge main document..."

    Previously I had presumed you had done that for us and it was one of the files downloaded, but now I think not. True?
    The tutorial contains a sample working field that you could paste into the accompanying mailmerge main document and use for that purpose - but only with the demo data file. It can't be used 'as is' with anything else.

    As for your other questions:
    1. Yes
    2 & 3. The 'borders' around the «City», «Representative» & «Sales» mergefields are ordinary table borders - the process requires a Word table. Each column represents one of the data fields that are to be included in the mailmerge output for each group. You would be able to see the entire structure if you exposed the field codes as described under 'Viewing The Fields' in the tutorial's Introduction. Tables, mergefields, etc. are standard features in Word and I wouldn't have expected this to need explanation. I don't propose to expand the tutorial to cover such basics.
    4. N/A
    5. Correct. This is an intermediate file created by the process. It is not your data source.
    6. You need to copy the entire field structure, delimited by the [ ], not just the «City», «Representative» & «Sales» mergefields. Again, see 'Viewing The Fields' and 'Using the Sample Data File & Mailmerge Main Document' in the tutorial's Introduction. It doesn't matter what name you give the Catalog/Directory mailmerge main document, provided you: a) save it in the same folder as your 'Email Merge Main Document'; and b) don't name it 'Email Merge Main Document' or 'EmailDataSource'.

    As for the screenshot in your last post, the «Recipient» field, that should only be added to the 'Email Merge Main Document' (which you don't connect to a data source) and even then only if you want the email address to appear in body of the email. I don't know what document you've added it to, as that document is evidently connected to a data source.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  9. #9
    New Lounger
    Join Date
    Aug 2016
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Most, most courteous Paul...will follow your instructions....I'm sure it's something simple. When coding I put a period in the wrong place or an extra space....
    Will keep you up to date..
    Thanks again for the education - most appreciated
    Mort in Dallas

Posting Permissions

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