#  Other Applications & Softwares  > Word Formatting & General >  > [SOLVED] Where do I find the mail merge vba code?

## brucemc777

I am working on learning how to create a mail merge from Excel, but to do so one needs a chunk of code that I need to create from different merges to study, and so far can not find where the heck the code is. As an example I extracted the salient block from another post as follows:




```
Please Login or Register  to view this content.
```


I need to learn how to define different DataSources and SQLStatements, which I hope to do by choosing different datasources and creating different searches and reading how this general block gets altered, just like learning from my own recorded macros.

As an added note, I intend to use this for eMail merges, for where it makes a difference, and the MSDN version/explanation(...) of MailMerge.OpenDataSource kind of flies right over my head. No, not "kind of". More like "absolutely".

Can this be done? If so, where the heck do I look?!? and if not, how can I learn how to accomplish this?

I am old, so yes, I am open to even purchasing and reading books...

Thank-you!

----------


## alansidman

try this tutorial

https://www.youtube.com/watch?v=0nBcxc5zt1Q

----------


## macropod

The following early-binding *Excel* macro runs a Word mailmerge.
The data source is defined by the string starting with 'strWorkbookName ='. As coded, it uses the workbook the macro is stored in.
The mailmerge main document is defined on the line starting with 'Set wdDoc ='. As coded, it uses a document named ‘MailMergeMainDocument’ stored in the same folder as the Excel workbook from which the macro is run as the mailmerge main document.
The mailmerge SQL query is defined on the line starting with Const strQry As String ='. As coded, it gets everything from a worksheet named 'Sheet1'.



```
Please Login or Register  to view this content.
```


If you actually do the setup of your mailmerge main document as such (so you can do all the correct mergefield insertions), the simplest method of identifying the data source and the connect & query strings it employs so you can add them to the macro above is to use a *Word* macro like:



```
Please Login or Register  to view this content.
```

----------


## brucemc777

Thank-you Gentlemen!

I am studying both the video and the code.

I find the video very interesting as his method would be great for the single-send "merges" that I *need* to incorporate. I now have to figure out if I can augment it to: Pull in some additional fields and incorporate them into the body of the text, andHow to direct the output to an email rather than printing envelopes.

Regarding sending to multple recipients using full mailmerge:

...and with all examples I have found using mailmerge they all appear to be outputting to documents; I need to define (in my head) what would re-direct the framework to be oriented to an email and direct output to an email.

Any help on these two methods are greatly appreciated!

----------


## macropod

Only a few minor coding changes are needed to send the outputs to email. Of course, had you said up-front that's what you were wanting to automate, I might have provided the appropriate code... 

Insert:
, i As Long
after:
wdDoc As Word.Document

Replace:



```
Please Login or Register  to view this content.
```


with:



```
Please Login or Register  to view this content.
```


Replace:



```
Please Login or Register  to view this content.
```


with:



```
Please Login or Register  to view this content.
```


Now all you need to do is provide the appropriate:
• 'MailSubject' details - which could even be in a field in the data source; and
• 'MailAddressFieldName' reference from the data source.

----------


## brucemc777

I had mistakenly thought that all that i needed was in that block i was asking about, but once again i in retrospect i find i just might have been wrong... Thank-you for coming to my rescue and enlightenment!!

----------


## macropod

Note that the additional code I posted has the line:
If Trim(.DataFields("Last_Name")) = "" Then Exit For
I should have explained that that code is to provide an exit if, as often happens with Excel workbooks, the used range extends beyond the data. You could use any suitable field name, rather than just one named 'Last_Name'.

Come to think of it, depending on what you're doing you may not even need the loop - just the change in type & destination, plus the parameters for the 'MailFormat', 'MailSubject' and 'MailAddressFieldName'.

----------


## brucemc777

Since I am now more among the living I belayed studying the code until this morning (still need another cup of coffee, but...). I find this most helpful and am learning much from it!

Yes, I noted that as an exit, and that I would need to match up field names. I also converted all that I understood necessary to late binding (the objects, and learned how to look up the enumerations). 

At present I do have a question about the SQL statement - the use of 'sheet1$' in the datasource - In the implementation of what I am working I first was going to filter a table and once happy with the filtered results, try to pump that out for the datasource. When I did the merge from Word step-by-step I could select the sheet by name and then do some matching of fields, as my field names were not on the top row of the sheet, they were in the top row of the table on that sheet (row 2...). 

I suspect it will be "cleaner" to now perform my filtering on the table then by code select the table range and copy only the visible cells in it to a sheet purposed for this that simply gets wiped clean each time it is used, starting with Row 1 for what was the HeaderRowRange, then replace what you have as 'sheet1$' with the name of that sheet. Does this seem like it will work or am I trying to reinvent the wheel? 

Next, can you advise if the reference: 


```
Please Login or Register  to view this content.
```


 might run into problems with different versions of Office? I run 2016, but my two friends both run 2007 (which is why I am using late instead of early binding - I have yet to research how I can integrate prior libraries, but it is on my "to-do" list...).

Also parenthetically wondering about the "$" at the end of the term 'Sheet1$' if you could let me know the purpose of that.

*Much* appreciate the ongoing support; I always try to find examples to learn from out there before asking!

----------


## cytop

Ignore - mouse bounce or more likely the ExcelForum lurgies...

----------


## cytop

SQL can extract data either from Excel Sheets or Named ranges (and probably a defined range like 'A100:F250 - but I've never had a need to use that).

If you are using a sheet as the source then that is identifed to the database driver with the '$' after the sheet name. If a named range is the source the '$' is omitted...





> I have yet to research how I can integrate prior libraries



Take it that you don't. The convention is develop on the older, slower machine with the earlier versions. References will always be upgraded automatically if moved to a machine with a later version, but will never be down-graded.

----------


## brucemc777

Running into a bit of an obstacle here.

As speculated, I copy all visible filtered records to another sheet that for better or worse I named "MailMerge". To make it simple I filtered so only one record would show (one with my email address) along with the HeaderRowRange to Worksheets("MailMerge"), which also is Sheet3.

I tried three variations on the line defining the SQL query:



```
Please Login or Register  to view this content.
```





```
Please Login or Register  to view this content.
```


and



```
Please Login or Register  to view this content.
```


in separate runs, and verified that "MailMerge" aka Sheet3 had the field names on row 01 and my record alone on row 02.

As none of the above worked I checked what I could. I found that wdDoc.name returned the name of the document correctly after being Set, but when I checked wdDoc.MailMerge.DataSource.RecordCount I came up with -1 every time.

In the event that I somehow messed another area of the code up, at the end of this I am posting the entire procedure as I have modified it. Are you able to advise where I have run off track?

(Note: In the code I use a comment with a mess of asterisks to flag my attention to areas that I will need to modify or will need some form of future attention)




```
Please Login or Register  to view this content.
```

----------


## Norie

_Your post does not comply with Rule 1 of our Forum_ RULES. *Your post title should accurately and concisely describe your problem, not your anticipated solution.*

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

*To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.*

*(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)*

----------


## brucemc777

@ Norie - The Title was constructed at the time my first question in the thread was posed as best as I thought would correspond. Since that time through the continuous help of some very patient people, I have learned that I was barking up the wrong tree and they have continued to give me support and redirect my attention in a continuing learning process, building upon each prior work. Yes, the point we are now at does deviate from where I started; does this mean as a concept is built upon one is to change the initial title of the post? I am glad to comply; I simply am a bit confused for in the beginning the initial response was to the post I presented, correcting my confusion, and the title was commensurate with the initial post to the best of my confusion.

You yourself have personally helped me in a number of instances; I mean no disrespect, simply clarification.

edit: Interesting enough (to me), we are back to that same block from my original post seemingly having a problem in my implementation! I still truly wish I could create different "instances" of it with varying Word mailmerges so I could learn from them-

----------


## brucemc777

I find that if I change the ReadOnly in:



```
Please Login or Register  to view this content.
```


to True, it does show the recordcount = 1 and when I test wdDoc.MailMerge.DataSource.DataFields("Company") I get the correct result.

Now to figure out what else is going wrong for me as it didn't pump out the email!

@ Norie, ctd.... I attempted to change the title using "edit": "Advanced" and did a change there, but it seemed to only affect the one post. When I returned to update findings, the title was the first one that was used upon starting this thread. Am I approaching this incorrectly? Can you assist?

----------


## macropod

> Running into a bit of an obstacle here.
> 
> As speculated, I copy all visible filtered records to another sheet that for better or worse I named "MailMerge". To make it simple I filtered so only one record would show (one with my email address) along with the HeaderRowRange to Worksheets("MailMerge"), which also is Sheet3.
> 
> I tried three variations on the line defining the SQL query



As I indicated in post #3, if you do the mailmerge setup in the normal manner using the document, you can then run the second macro I posted there to extract these details.

PS: I fail to see how the thread title was ever non-compliant.

----------


## brucemc777

@macropod - I suspect I might have been trying to rush it all; I've been taking in a lot of new information from you, alansidman and cytop and trying to assimilate it all while dealing with bringing two daughters back from college (adding to the two at home so I am hopelessly outnumbered). Yes, another excuse. But not a bad one...

Anyway, thank-you, very much. I will go back to Post #3.

----------


## brucemc777

@macropod - I suspect I might have been trying to rush it all; I've been taking in a lot of new information from you, alansidman and cytop and trying to assimilate it all while dealing with bringing two daughters back from college (adding to the two at home so I am hopelessly outnumbered). Yes, another excuse. But not a bad one...

Anyway, thank-you, very much. I will go back to Post #3.

edit: I went back to Post #3: Embarrassment...

Also, still trying to define why the email(s) are not sending. Picking through everything to get a deeper understanding I notice that wdMergeSupType is set to Access. As I see nothing for Excel in the types I suspect this is the equivalent setting but at this point I don't trust in my suspicions and thought I should ask-

edit # 2Another "Ah-ha!" moment...
My misinterpretation of 


```
Please Login or Register  to view this content.
```


With warnings/Alerts suppressed I was going through and educating myself line-by-line on this, then when I was almost there anyway I allowed alerts to be on, so I got my SQL notice, but then I ran into an alert advising that I needed a valid email address. As I was almost there anyway I was picking through details looking up MailAddressFieldName, MailSubject and MailFormat, which was when my dang bad presumption struck me like getting hit in the head.

Thank-you folks for sticking with me, very, very much. Gonna close this one out!

(That just means I will open a new thread with whatever I trip over next...)

----------


## brucemc777

I probably need to start a new thread at this point, but because you folks specifically have been so helpful and are familiar with the code -

Is there a way for me to specify an attachment in the code "we" (you) have created?

----------


## macropod

If you want to send the mailmerge output as an attachment, you can use '.MailAsAttachment = True' but, if you want to attach another document, you're going to have to automate Outlook - a whole different ballgame. See, for example: http://word.mvps.org/FAQs/MailMerge/...ttachments.htm

----------


## brucemc777

Understood; I was hoping for a solution without going the Outlook route after seeing that as the solution in every post I read, knew that if there was a Word solution, you would know.

Thank-you!

----------

