| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Email Merge in Word
I have a list of email address in an 2007 Access query. I would like to
individually email merge articles I have written in MS Word and send them thru MS Outlook individually so they are professional sent with their first and last name on to To: field. How can this be done successfully? Many thanks for your help |
|
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Email Merge in Word
Do you mean rather than their email address?
-- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP, originally posted via msnews.microsoft.com "Susan May" wrote in message ... I have a list of email address in an 2007 Access query. I would like to individually email merge articles I have written in MS Word and send them thru MS Outlook individually so they are professional sent with their first and last name on to To: field. How can this be done successfully? Many thanks for your help |
|
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Email Merge in Word
Doug, I want to take a list of emails I have in a query and individually send
them out so it looks like I'm sending them individually, but I'm actually sending them using a Word email merge because what I'm sending them are articles that we publish about the economy and these are potential recruits that have contacted us for more information but have not joined our firm yet. I don't want to copy and paste in bcc field in Outlook because that would make me look like a blaster and I don't want our server to be black listed. We have the rights to these emails so I was told by doing an email merge in Word, I can set it up so it would go out to each one individually and not alert the internet providers. Also, when I set up my query, I selected the criteria for the email address field "is not null", and there are still some blank records with no emails. Why is that happening? There are not many, but is there a better syntax to use to get rid of the records that do not have email addresses? Many thanks. PS Lately, when I've been notified of a response from my questions, the link does not take me to the page where the question is. It is blank - do you know why this is happening? Susan "Doug Robbins - Word MVP on news.microsof" wrote: Do you mean rather than their email address? -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP, originally posted via msnews.microsoft.com "Susan May" wrote in message ... I have a list of email address in an 2007 Access query. I would like to individually email merge articles I have written in MS Word and send them thru MS Outlook individually so they are professional sent with their first and last name on to To: field. How can this be done successfully? Many thanks for your help |
|
#4
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Email Merge in Word
Also, when I set up my query, I selected the criteria for the email
address field "is not null", and there are still some blank records with no emails. Different software packages can treat "null", "a string set to ''", and "a string containing white space differently, and may also treat variable-length and fixed-length data differently in this respect. As far as Access is concerned, in some cases if you enter "white space", Access leaves the field value as "null". However, if you set a text value to '' in code, it isn't regarded as null but will otherwise look no different to the user in many cases. So generally speaking, you have to test a string for both null and blank. to detect that in the SQL in Access you need something like WHERE mystring is not null AND mystring '' If the string could contain one or more spaces, you would probably need to add another condition, e.g. WHERE mystring is not null AND mystring '' AND trim(mystring) '' but if mystring could contain other types of "whitespace" such as tabs, non-breaking spaces, I think you would need to ask an Access person. You might be able to use LIKE with a pattern to do it. Peter Jamieson http://tips.pjmsn.me.uk Susan May wrote: Doug, I want to take a list of emails I have in a query and individually send them out so it looks like I'm sending them individually, but I'm actually sending them using a Word email merge because what I'm sending them are articles that we publish about the economy and these are potential recruits that have contacted us for more information but have not joined our firm yet. I don't want to copy and paste in bcc field in Outlook because that would make me look like a blaster and I don't want our server to be black listed. We have the rights to these emails so I was told by doing an email merge in Word, I can set it up so it would go out to each one individually and not alert the internet providers. Also, when I set up my query, I selected the criteria for the email address field "is not null", and there are still some blank records with no emails. Why is that happening? There are not many, but is there a better syntax to use to get rid of the records that do not have email addresses? Many thanks. PS Lately, when I've been notified of a response from my questions, the link does not take me to the page where the question is. It is blank - do you know why this is happening? Susan "Doug Robbins - Word MVP on news.microsof" wrote: Do you mean rather than their email address? -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP, originally posted via msnews.microsoft.com "Susan May" wrote in message ... I have a list of email address in an 2007 Access query. I would like to individually email merge articles I have written in MS Word and send them thru MS Outlook individually so they are professional sent with their first and last name on to To: field. How can this be done successfully? Many thanks for your help |
|
#5
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Email Merge in Word
Peter: I tried this WHERE mystring is not null AND mystring '' AND trim(mystring) '' with mystring name as Email1Address, and it created 3 different columns with the criteria "is not null"; "; and Trim(Emai1lAddress) with criteria ", and I'm still getting blank emails. What did I do wrong? "Peter Jamieson" wrote: Also, when I set up my query, I selected the criteria for the email address field "is not null", and there are still some blank records with no emails. Different software packages can treat "null", "a string set to ''", and "a string containing white space differently, and may also treat variable-length and fixed-length data differently in this respect. As far as Access is concerned, in some cases if you enter "white space", Access leaves the field value as "null". However, if you set a text value to '' in code, it isn't regarded as null but will otherwise look no different to the user in many cases. So generally speaking, you have to test a string for both null and blank. to detect that in the SQL in Access you need something like WHERE mystring is not null AND mystring '' If the string could contain one or more spaces, you would probably need to add another condition, e.g. WHERE mystring is not null AND mystring '' AND trim(mystring) '' but if mystring could contain other types of "whitespace" such as tabs, non-breaking spaces, I think you would need to ask an Access person. You might be able to use LIKE with a pattern to do it. Peter Jamieson http://tips.pjmsn.me.uk Susan May wrote: Doug, I want to take a list of emails I have in a query and individually send them out so it looks like I'm sending them individually, but I'm actually sending them using a Word email merge because what I'm sending them are articles that we publish about the economy and these are potential recruits that have contacted us for more information but have not joined our firm yet. I don't want to copy and paste in bcc field in Outlook because that would make me look like a blaster and I don't want our server to be black listed. We have the rights to these emails so I was told by doing an email merge in Word, I can set it up so it would go out to each one individually and not alert the internet providers. Also, when I set up my query, I selected the criteria for the email address field "is not null", and there are still some blank records with no emails. Why is that happening? There are not many, but is there a better syntax to use to get rid of the records that do not have email addresses? Many thanks. PS Lately, when I've been notified of a response from my questions, the link does not take me to the page where the question is. It is blank - do you know why this is happening? Susan "Doug Robbins - Word MVP on news.microsof" wrote: Do you mean rather than their email address? -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP, originally posted via msnews.microsoft.com "Susan May" wrote in message ... I have a list of email address in an 2007 Access query. I would like to individually email merge articles I have written in MS Word and send them thru MS Outlook individually so they are professional sent with their first and last name on to To: field. How can this be done successfully? Many thanks for your help |
|
#6
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Email Merge in Word
In the Query output field use Trim(EmailAddress) and set the Criteria to Is
Not Null -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP, originally posted via msnews.microsoft.com "Susan May" wrote in message news  Peter: I tried this WHERE mystring is not null AND mystring '' AND trim(mystring) '' with mystring name as Email1Address, and it created 3 different columns with the criteria "is not null"; "; and Trim(Emai1lAddress) with criteria ", and I'm still getting blank emails. What did I do wrong? "Peter Jamieson" wrote: Also, when I set up my query, I selected the criteria for the email address field "is not null", and there are still some blank records with no emails. Different software packages can treat "null", "a string set to ''", and "a string containing white space differently, and may also treat variable-length and fixed-length data differently in this respect. As far as Access is concerned, in some cases if you enter "white space", Access leaves the field value as "null". However, if you set a text value to '' in code, it isn't regarded as null but will otherwise look no different to the user in many cases. So generally speaking, you have to test a string for both null and blank. to detect that in the SQL in Access you need something like WHERE mystring is not null AND mystring '' If the string could contain one or more spaces, you would probably need to add another condition, e.g. WHERE mystring is not null AND mystring '' AND trim(mystring) '' but if mystring could contain other types of "whitespace" such as tabs, non-breaking spaces, I think you would need to ask an Access person. You might be able to use LIKE with a pattern to do it. Peter Jamieson http://tips.pjmsn.me.uk Susan May wrote: Doug, I want to take a list of emails I have in a query and individually send them out so it looks like I'm sending them individually, but I'm actually sending them using a Word email merge because what I'm sending them are articles that we publish about the economy and these are potential recruits that have contacted us for more information but have not joined our firm yet. I don't want to copy and paste in bcc field in Outlook because that would make me look like a blaster and I don't want our server to be black listed. We have the rights to these emails so I was told by doing an email merge in Word, I can set it up so it would go out to each one individually and not alert the internet providers. Also, when I set up my query, I selected the criteria for the email address field "is not null", and there are still some blank records with no emails. Why is that happening? There are not many, but is there a better syntax to use to get rid of the records that do not have email addresses? Many thanks. PS Lately, when I've been notified of a response from my questions, the link does not take me to the page where the question is. It is blank - do you know why this is happening? Susan "Doug Robbins - Word MVP on news.microsof" wrote: Do you mean rather than their email address? -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP, originally posted via msnews.microsoft.com "Susan May" wrote in message ... I have a list of email address in an 2007 Access query. I would like to individually email merge articles I have written in MS Word and send them thru MS Outlook individually so they are professional sent with their first and last name on to To: field. How can this be done successfully? Many thanks for your help |
|
#7
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Email Merge in Word
1. FWIW you can probably reduce the criteria I suggested to
WHERE mystring is not null AND trim(mystring) '' since the other condition should be taken care of by the trim() condition. 2. I think the next thing to do is check what's actually in that field in some of the records that should be eliminated. (I tend to work directly on the SQL, but if you are more familiar with the graphical designer I'd try to stick with that). 3. e.g. add a few columns to your query such as len(trim(Email1Address)) asc(mid(trim(Email1Address),1,1)) If the address looks blank but len(trim(Email1Address)) is 0 then the field probably contains invisible non-space characters. The asc() function should tell us what the first character is. You can use asc(mid(trim(Email1Address),2,1)) etc. to look at the other characters. Peter Jamieson http://tips.pjmsn.me.uk Susan May wrote: Peter: I tried this WHERE mystring is not null AND mystring '' AND trim(mystring) '' with mystring name as Email1Address, and it created 3 different columns with the criteria "is not null"; "; and Trim(Emai1lAddress) with criteria ", and I'm still getting blank emails. What did I do wrong? "Peter Jamieson" wrote: Also, when I set up my query, I selected the criteria for the email address field "is not null", and there are still some blank records with no emails. Different software packages can treat "null", "a string set to ''", and "a string containing white space differently, and may also treat variable-length and fixed-length data differently in this respect. As far as Access is concerned, in some cases if you enter "white space", Access leaves the field value as "null". However, if you set a text value to '' in code, it isn't regarded as null but will otherwise look no different to the user in many cases. So generally speaking, you have to test a string for both null and blank. to detect that in the SQL in Access you need something like WHERE mystring is not null AND mystring '' If the string could contain one or more spaces, you would probably need to add another condition, e.g. WHERE mystring is not null AND mystring '' AND trim(mystring) '' but if mystring could contain other types of "whitespace" such as tabs, non-breaking spaces, I think you would need to ask an Access person. You might be able to use LIKE with a pattern to do it. Peter Jamieson http://tips.pjmsn.me.uk Susan May wrote: Doug, I want to take a list of emails I have in a query and individually send them out so it looks like I'm sending them individually, but I'm actually sending them using a Word email merge because what I'm sending them are articles that we publish about the economy and these are potential recruits that have contacted us for more information but have not joined our firm yet. I don't want to copy and paste in bcc field in Outlook because that would make me look like a blaster and I don't want our server to be black listed. We have the rights to these emails so I was told by doing an email merge in Word, I can set it up so it would go out to each one individually and not alert the internet providers. Also, when I set up my query, I selected the criteria for the email address field "is not null", and there are still some blank records with no emails. Why is that happening? There are not many, but is there a better syntax to use to get rid of the records that do not have email addresses? Many thanks. PS Lately, when I've been notified of a response from my questions, the link does not take me to the page where the question is. It is blank - do you know why this is happening? Susan "Doug Robbins - Word MVP on news.microsof" wrote: Do you mean rather than their email address? -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP, originally posted via msnews.microsoft.com "Susan May" wrote in message ... I have a list of email address in an 2007 Access query. I would like to individually email merge articles I have written in MS Word and send them thru MS Outlook individually so they are professional sent with their first and last name on to To: field. How can this be done successfully? Many thanks for your help |
|
#8
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Email Merge in Word
Peter when I do this WHERE mystring is not null AND trim(mystring) '' I
get an error message about the string 2048 characters so it doesn't work. I do this - len(trim(Email1Address)) asc(mid(trim(Email1Address),1,1)) and no records appear. What am I doing wrong? Susan "Peter Jamieson" wrote: 1. FWIW you can probably reduce the criteria I suggested to WHERE mystring is not null AND trim(mystring) '' since the other condition should be taken care of by the trim() condition. 2. I think the next thing to do is check what's actually in that field in some of the records that should be eliminated. (I tend to work directly on the SQL, but if you are more familiar with the graphical designer I'd try to stick with that). 3. e.g. add a few columns to your query such as len(trim(Email1Address)) asc(mid(trim(Email1Address),1,1)) If the address looks blank but len(trim(Email1Address)) is 0 then the field probably contains invisible non-space characters. The asc() function should tell us what the first character is. You can use asc(mid(trim(Email1Address),2,1)) etc. to look at the other characters. Peter Jamieson http://tips.pjmsn.me.uk Susan May wrote: Peter: I tried this WHERE mystring is not null AND mystring '' AND trim(mystring) '' with mystring name as Email1Address, and it created 3 different columns with the criteria "is not null"; "; and Trim(Emai1lAddress) with criteria ", and I'm still getting blank emails. What did I do wrong? "Peter Jamieson" wrote: Also, when I set up my query, I selected the criteria for the email address field "is not null", and there are still some blank records with no emails. Different software packages can treat "null", "a string set to ''", and "a string containing white space differently, and may also treat variable-length and fixed-length data differently in this respect. As far as Access is concerned, in some cases if you enter "white space", Access leaves the field value as "null". However, if you set a text value to '' in code, it isn't regarded as null but will otherwise look no different to the user in many cases. So generally speaking, you have to test a string for both null and blank. to detect that in the SQL in Access you need something like WHERE mystring is not null AND mystring '' If the string could contain one or more spaces, you would probably need to add another condition, e.g. WHERE mystring is not null AND mystring '' AND trim(mystring) '' but if mystring could contain other types of "whitespace" such as tabs, non-breaking spaces, I think you would need to ask an Access person. You might be able to use LIKE with a pattern to do it. Peter Jamieson http://tips.pjmsn.me.uk Susan May wrote: Doug, I want to take a list of emails I have in a query and individually send them out so it looks like I'm sending them individually, but I'm actually sending them using a Word email merge because what I'm sending them are articles that we publish about the economy and these are potential recruits that have contacted us for more information but have not joined our firm yet. I don't want to copy and paste in bcc field in Outlook because that would make me look like a blaster and I don't want our server to be black listed. We have the rights to these emails so I was told by doing an email merge in Word, I can set it up so it would go out to each one individually and not alert the internet providers. Also, when I set up my query, I selected the criteria for the email address field "is not null", and there are still some blank records with no emails. Why is that happening? There are not many, but is there a better syntax to use to get rid of the records that do not have email addresses? Many thanks. PS Lately, when I've been notified of a response from my questions, the link does not take me to the page where the question is. It is blank - do you know why this is happening? Susan "Doug Robbins - Word MVP on news.microsof" wrote: Do you mean rather than their email address? -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP, originally posted via msnews.microsoft.com "Susan May" wrote in message ... I have a list of email address in an 2007 Access query. I would like to individually email merge articles I have written in MS Word and send them thru MS Outlook individually so they are professional sent with their first and last name on to To: field. How can this be done successfully? Many thanks for your help |
|
#9
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Email Merge in Word
Doug - no records appear when I enter in your string. What am I doing wrong?
Susan "Doug Robbins - Word MVP on news.microsof" wrote: Do you mean rather than their email address? -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP, originally posted via msnews.microsoft.com "Susan May" wrote in message ... I have a list of email address in an 2007 Access query. I would like to individually email merge articles I have written in MS Word and send them thru MS Outlook individually so they are professional sent with their first and last name on to To: field. How can this be done successfully? Many thanks for your help |
|
#10
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Email Merge in Word
Susan,
What am I doing wrong? I don't know - not necessarily anything. NB I was intending these test queries to be executed entirely within Access - is that what you are doing? Can you please a. look at the SQL for your query (if you haven't done this before, try right-clicking in the title bar of the query designer and selecting the SQL option) and copy/paste a copy here? b. tell us exactly what the error message says? Peter Jamieson http://tips.pjmsn.me.uk Susan May wrote: Peter when I do this WHERE mystring is not null AND trim(mystring) '' I get an error message about the string 2048 characters so it doesn't work. I do this - len(trim(Email1Address)) asc(mid(trim(Email1Address),1,1)) and no records appear. What am I doing wrong? Susan "Peter Jamieson" wrote: 1. FWIW you can probably reduce the criteria I suggested to WHERE mystring is not null AND trim(mystring) '' since the other condition should be taken care of by the trim() condition. 2. I think the next thing to do is check what's actually in that field in some of the records that should be eliminated. (I tend to work directly on the SQL, but if you are more familiar with the graphical designer I'd try to stick with that). 3. e.g. add a few columns to your query such as len(trim(Email1Address)) asc(mid(trim(Email1Address),1,1)) If the address looks blank but len(trim(Email1Address)) is 0 then the field probably contains invisible non-space characters. The asc() function should tell us what the first character is. You can use asc(mid(trim(Email1Address),2,1)) etc. to look at the other characters. Peter Jamieson http://tips.pjmsn.me.uk Susan May wrote: Peter: I tried this WHERE mystring is not null AND mystring '' AND trim(mystring) '' with mystring name as Email1Address, and it created 3 different columns with the criteria "is not null"; "; and Trim(Emai1lAddress) with criteria ", and I'm still getting blank emails. What did I do wrong? "Peter Jamieson" wrote: Also, when I set up my query, I selected the criteria for the email address field "is not null", and there are still some blank records with no emails. Different software packages can treat "null", "a string set to ''", and "a string containing white space differently, and may also treat variable-length and fixed-length data differently in this respect. As far as Access is concerned, in some cases if you enter "white space", Access leaves the field value as "null". However, if you set a text value to '' in code, it isn't regarded as null but will otherwise look no different to the user in many cases. So generally speaking, you have to test a string for both null and blank. to detect that in the SQL in Access you need something like WHERE mystring is not null AND mystring '' If the string could contain one or more spaces, you would probably need to add another condition, e.g. WHERE mystring is not null AND mystring '' AND trim(mystring) '' but if mystring could contain other types of "whitespace" such as tabs, non-breaking spaces, I think you would need to ask an Access person. You might be able to use LIKE with a pattern to do it. Peter Jamieson http://tips.pjmsn.me.uk Susan May wrote: Doug, I want to take a list of emails I have in a query and individually send them out so it looks like I'm sending them individually, but I'm actually sending them using a Word email merge because what I'm sending them are articles that we publish about the economy and these are potential recruits that have contacted us for more information but have not joined our firm yet. I don't want to copy and paste in bcc field in Outlook because that would make me look like a blaster and I don't want our server to be black listed. We have the rights to these emails so I was told by doing an email merge in Word, I can set it up so it would go out to each one individually and not alert the internet providers. Also, when I set up my query, I selected the criteria for the email address field "is not null", and there are still some blank records with no emails. Why is that happening? There are not many, but is there a better syntax to use to get rid of the records that do not have email addresses? Many thanks. PS Lately, when I've been notified of a response from my questions, the link does not take me to the page where the question is. It is blank - do you know why this is happening? Susan "Doug Robbins - Word MVP on news.microsof" wrote: Do you mean rather than their email address? -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP, originally posted via msnews.microsoft.com "Susan May" wrote in message ... I have a list of email address in an 2007 Access query. I would like to individually email merge articles I have written in MS Word and send them thru MS Outlook individually so they are professional sent with their first and last name on to To: field. How can this be done successfully? Many thanks for your help |
|
#11
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Email Merge in Word
Here Peter:
SELECT [DD2 LIST].Email1Address, [FirstName] & " " & [LastName] AS MergedName FROM [DD2 LIST] WHERE ((([DD2 LIST].Email1Address)=Len(Trim("Email1Address")) Or ([DD2 LIST].Email1Address)=Asc(Mid(Trim("Email1Address"),1,1) ))); "Peter Jamieson" wrote: Susan, What am I doing wrong? I don't know - not necessarily anything. NB I was intending these test queries to be executed entirely within Access - is that what you are doing? Can you please a. look at the SQL for your query (if you haven't done this before, try right-clicking in the title bar of the query designer and selecting the SQL option) and copy/paste a copy here? b. tell us exactly what the error message says? Peter Jamieson http://tips.pjmsn.me.uk Susan May wrote: Peter when I do this WHERE mystring is not null AND trim(mystring) '' I get an error message about the string 2048 characters so it doesn't work. I do this - len(trim(Email1Address)) asc(mid(trim(Email1Address),1,1)) and no records appear. What am I doing wrong? Susan "Peter Jamieson" wrote: 1. FWIW you can probably reduce the criteria I suggested to WHERE mystring is not null AND trim(mystring) '' since the other condition should be taken care of by the trim() condition. 2. I think the next thing to do is check what's actually in that field in some of the records that should be eliminated. (I tend to work directly on the SQL, but if you are more familiar with the graphical designer I'd try to stick with that). 3. e.g. add a few columns to your query such as len(trim(Email1Address)) asc(mid(trim(Email1Address),1,1)) If the address looks blank but len(trim(Email1Address)) is 0 then the field probably contains invisible non-space characters. The asc() function should tell us what the first character is. You can use asc(mid(trim(Email1Address),2,1)) etc. to look at the other characters. Peter Jamieson http://tips.pjmsn.me.uk Susan May wrote: Peter: I tried this WHERE mystring is not null AND mystring '' AND trim(mystring) '' with mystring name as Email1Address, and it created 3 different columns with the criteria "is not null"; "; and Trim(Emai1lAddress) with criteria ", and I'm still getting blank emails. What did I do wrong? "Peter Jamieson" wrote: Also, when I set up my query, I selected the criteria for the email address field "is not null", and there are still some blank records with no emails. Different software packages can treat "null", "a string set to ''", and "a string containing white space differently, and may also treat variable-length and fixed-length data differently in this respect. As far as Access is concerned, in some cases if you enter "white space", Access leaves the field value as "null". However, if you set a text value to '' in code, it isn't regarded as null but will otherwise look no different to the user in many cases. So generally speaking, you have to test a string for both null and blank. to detect that in the SQL in Access you need something like WHERE mystring is not null AND mystring '' If the string could contain one or more spaces, you would probably need to add another condition, e.g. WHERE mystring is not null AND mystring '' AND trim(mystring) '' but if mystring could contain other types of "whitespace" such as tabs, non-breaking spaces, I think you would need to ask an Access person. You might be able to use LIKE with a pattern to do it. Peter Jamieson http://tips.pjmsn.me.uk Susan May wrote: Doug, I want to take a list of emails I have in a query and individually send them out so it looks like I'm sending them individually, but I'm actually sending them using a Word email merge because what I'm sending them are articles that we publish about the economy and these are potential recruits that have contacted us for more information but have not joined our firm yet. I don't want to copy and paste in bcc field in Outlook because that would make me look like a blaster and I don't want our server to be black listed. We have the rights to these emails so I was told by doing an email merge in Word, I can set it up so it would go out to each one individually and not alert the internet providers. Also, when I set up my query, I selected the criteria for the email address field "is not null", and there are still some blank records with no emails. Why is that happening? There are not many, but is there a better syntax to use to get rid of the records that do not have email addresses? Many thanks. PS Lately, when I've been notified of a response from my questions, the link does not take me to the page where the question is. It is blank - do you know why this is happening? Susan "Doug Robbins - Word MVP on news.microsof" wrote: Do you mean rather than their email address? -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP, originally posted via msnews.microsoft.com "Susan May" wrote in message ... I have a list of email address in an 2007 Access query. I would like to individually email merge articles I have written in MS Word and send them thru MS Outlook individually so they are professional sent with their first and last name on to To: field. How can this be done successfully? Many thanks for your help |
|
#12
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Email Merge in Word
Hi Susan,
1. In that SQL pane, can you first try replacing what you have with SELECT [DD2 LIST].Email1Address, [DD2 LIST].[FirstName] & " " & [DD2 LIST].[LastName] AS MergedName FROM [DD2 LIST] WHERE [DD2 LIST].Email1Address IS NOT NULL AND trim([DD2 LIST].Email1Address) "" which is the query I think you should be running, so a. try executing that b. if you want, see how that "translates" to the settings in the visual designer (at this point you may find it easier to work in the SQL pane) 2. If that does not function, try SELECT [DD2 LIST].Email1Address, [DD2 LIST].[FirstName] & " " & [DD2 LIST].[LastName] AS MergedName, Len(Trim("Email1Address")), Asc(Mid(Trim("Email1Address"),1,1)) FROM [DD2 LIST] which may give some further info. Peter Jamieson http://tips.pjmsn.me.uk Susan May wrote: Here Peter: SELECT [DD2 LIST].Email1Address, [FirstName] & " " & [LastName] AS MergedName FROM [DD2 LIST] WHERE ((([DD2 LIST].Email1Address)=Len(Trim("Email1Address")) Or ([DD2 LIST].Email1Address)=Asc(Mid(Trim("Email1Address"),1,1) ))); "Peter Jamieson" wrote: Susan, What am I doing wrong? I don't know - not necessarily anything. NB I was intending these test queries to be executed entirely within Access - is that what you are doing? Can you please a. look at the SQL for your query (if you haven't done this before, try right-clicking in the title bar of the query designer and selecting the SQL option) and copy/paste a copy here? b. tell us exactly what the error message says? Peter Jamieson http://tips.pjmsn.me.uk Susan May wrote: Peter when I do this WHERE mystring is not null AND trim(mystring) '' I get an error message about the string 2048 characters so it doesn't work. I do this - len(trim(Email1Address)) asc(mid(trim(Email1Address),1,1)) and no records appear. What am I doing wrong? Susan "Peter Jamieson" wrote: 1. FWIW you can probably reduce the criteria I suggested to WHERE mystring is not null AND trim(mystring) '' since the other condition should be taken care of by the trim() condition. 2. I think the next thing to do is check what's actually in that field in some of the records that should be eliminated. (I tend to work directly on the SQL, but if you are more familiar with the graphical designer I'd try to stick with that). 3. e.g. add a few columns to your query such as len(trim(Email1Address)) asc(mid(trim(Email1Address),1,1)) If the address looks blank but len(trim(Email1Address)) is 0 then the field probably contains invisible non-space characters. The asc() function should tell us what the first character is. You can use asc(mid(trim(Email1Address),2,1)) etc. to look at the other characters. Peter Jamieson http://tips.pjmsn.me.uk Susan May wrote: Peter: I tried this WHERE mystring is not null AND mystring '' AND trim(mystring) '' with mystring name as Email1Address, and it created 3 different columns with the criteria "is not null"; "; and Trim(Emai1lAddress) with criteria ", and I'm still getting blank emails. What did I do wrong? "Peter Jamieson" wrote: Also, when I set up my query, I selected the criteria for the email address field "is not null", and there are still some blank records with no emails. Different software packages can treat "null", "a string set to ''", and "a string containing white space differently, and may also treat variable-length and fixed-length data differently in this respect. As far as Access is concerned, in some cases if you enter "white space", Access leaves the field value as "null". However, if you set a text value to '' in code, it isn't regarded as null but will otherwise look no different to the user in many cases. So generally speaking, you have to test a string for both null and blank. to detect that in the SQL in Access you need something like WHERE mystring is not null AND mystring '' If the string could contain one or more spaces, you would probably need to add another condition, e.g. WHERE mystring is not null AND mystring '' AND trim(mystring) '' but if mystring could contain other types of "whitespace" such as tabs, non-breaking spaces, I think you would need to ask an Access person. You might be able to use LIKE with a pattern to do it. Peter Jamieson http://tips.pjmsn.me.uk Susan May wrote: Doug, I want to take a list of emails I have in a query and individually send them out so it looks like I'm sending them individually, but I'm actually sending them using a Word email merge because what I'm sending them are articles that we publish about the economy and these are potential recruits that have contacted us for more information but have not joined our firm yet. I don't want to copy and paste in bcc field in Outlook because that would make me look like a blaster and I don't want our server to be black listed. We have the rights to these emails so I was told by doing an email merge in Word, I can set it up so it would go out to each one individually and not alert the internet providers. Also, when I set up my query, I selected the criteria for the email address field "is not null", and there are still some blank records with no emails. Why is that happening? There are not many, but is there a better syntax to use to get rid of the records that do not have email addresses? Many thanks. PS Lately, when I've been notified of a response from my questions, the link does not take me to the page where the question is. It is blank - do you know why this is happening? Susan "Doug Robbins - Word MVP on news.microsof" wrote: Do you mean rather than their email address? -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP, originally posted via msnews.microsoft.com "Susan May" wrote in message ... I have a list of email address in an 2007 Access query. I would like to individually email merge articles I have written in MS Word and send them thru MS Outlook individually so they are professional sent with their first and last name on to To: field. How can this be done successfully? Many thanks for your help |
|
#13
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Email Merge in Word
Peter, it gives me DD2 - Enter Parameter Value and I click ok, and the blank
emails are still in there, but when I looked in the design view, under merged name, this is what it shows. MergedName: [DD2 LIST].[FirstName] & " " & [DD2 I tried to finish the string and it tells me the expression you entered contains invalid syntax. You may have entered an operand without an operator MergedName: [DD2 LIST].[FirstName] & " " & [DD2 LIST].[LastName] LIST].[LastName] This above is what I have now and the query will not run. This is so frustrating - you'd think this should be so simple in Access and all you should have to do is say is not null under Email1Address, but it's not. Help! "Peter Jamieson" wrote: Hi Susan, 1. In that SQL pane, can you first try replacing what you have with SELECT [DD2 LIST].Email1Address, [DD2 LIST].[FirstName] & " " & [DD2 LIST].[LastName] AS MergedName FROM [DD2 LIST] WHERE [DD2 LIST].Email1Address IS NOT NULL AND trim([DD2 LIST].Email1Address) "" which is the query I think you should be running, so a. try executing that b. if you want, see how that "translates" to the settings in the visual designer (at this point you may find it easier to work in the SQL pane) 2. If that does not function, try SELECT [DD2 LIST].Email1Address, [DD2 LIST].[FirstName] & " " & [DD2 LIST].[LastName] AS MergedName, Len(Trim("Email1Address")), Asc(Mid(Trim("Email1Address"),1,1)) FROM [DD2 LIST] which may give some further info. Peter Jamieson http://tips.pjmsn.me.uk Susan May wrote: Here Peter: SELECT [DD2 LIST].Email1Address, [FirstName] & " " & [LastName] AS MergedName FROM [DD2 LIST] WHERE ((([DD2 LIST].Email1Address)=Len(Trim("Email1Address")) Or ([DD2 LIST].Email1Address)=Asc(Mid(Trim("Email1Address"),1,1) ))); "Peter Jamieson" wrote: Susan, What am I doing wrong? I don't know - not necessarily anything. NB I was intending these test queries to be executed entirely within Access - is that what you are doing? Can you please a. look at the SQL for your query (if you haven't done this before, try right-clicking in the title bar of the query designer and selecting the SQL option) and copy/paste a copy here? b. tell us exactly what the error message says? Peter Jamieson http://tips.pjmsn.me.uk Susan May wrote: Peter when I do this WHERE mystring is not null AND trim(mystring) '' I get an error message about the string 2048 characters so it doesn't work. I do this - len(trim(Email1Address)) asc(mid(trim(Email1Address),1,1)) and no records appear. What am I doing wrong? Susan "Peter Jamieson" wrote: 1. FWIW you can probably reduce the criteria I suggested to WHERE mystring is not null AND trim(mystring) '' since the other condition should be taken care of by the trim() condition. 2. I think the next thing to do is check what's actually in that field in some of the records that should be eliminated. (I tend to work directly on the SQL, but if you are more familiar with the graphical designer I'd try to stick with that). 3. e.g. add a few columns to your query such as len(trim(Email1Address)) asc(mid(trim(Email1Address),1,1)) If the address looks blank but len(trim(Email1Address)) is 0 then the field probably contains invisible non-space characters. The asc() function should tell us what the first character is. You can use asc(mid(trim(Email1Address),2,1)) etc. to look at the other characters. Peter Jamieson http://tips.pjmsn.me.uk Susan May wrote: Peter: I tried this WHERE mystring is not null AND mystring '' AND trim(mystring) '' with mystring name as Email1Address, and it created 3 different columns with the criteria "is not null"; "; and Trim(Emai1lAddress) with criteria ", and I'm still getting blank emails. What did I do wrong? "Peter Jamieson" wrote: Also, when I set up my query, I selected the criteria for the email address field "is not null", and there are still some blank records with no emails. Different software packages can treat "null", "a string set to ''", and "a string containing white space differently, and may also treat variable-length and fixed-length data differently in this respect. As far as Access is concerned, in some cases if you enter "white space", Access leaves the field value as "null". However, if you set a text value to '' in code, it isn't regarded as null but will otherwise look no different to the user in many cases. So generally speaking, you have to test a string for both null and blank. to detect that in the SQL in Access you need something like WHERE mystring is not null AND mystring '' If the string could contain one or more spaces, you would probably need to add another condition, e.g. WHERE mystring is not null AND mystring '' AND trim(mystring) '' but if mystring could contain other types of "whitespace" such as tabs, non-breaking spaces, I think you would need to ask an Access person. You might be able to use LIKE with a pattern to do it. Peter Jamieson http://tips.pjmsn.me.uk Susan May wrote: Doug, I want to take a list of emails I have in a query and individually send them out so it looks like I'm sending them individually, but I'm actually sending them using a Word email merge because what I'm sending them are articles that we publish about the economy and these are potential recruits that have contacted us for more information but have not joined our firm yet. I don't want to copy and paste in bcc field in Outlook because that would make me look like a blaster and I don't want our server to be black listed. We have the rights to these emails so I was told by doing an email merge in Word, I can set it up so it would go out to each one individually and not alert the internet providers. Also, when I set up my query, I selected the criteria for the email address field "is not null", and there are still some blank records with no emails. Why is that happening? There are not many, but is there a better syntax to use to get rid of the records that do not have email addresses? Many thanks. PS Lately, when I've been notified of a response from my questions, the link does not take me to the page where the question is. It is blank - do you know why this is happening? Susan "Doug Robbins - Word MVP on news.microsof" wrote: Do you mean rather than their email address? -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP, originally posted via msnews.microsoft.com "Susan May" wrote in message ... I have a list of email address in an 2007 Access query. I would like to individually email merge articles I have written in MS Word and send them thru MS Outlook individually so they are professional sent with their first and last name on to To: field. How can this be done successfully? Many thanks for your help |
|
#14
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Email Merge in Word
This is so
frustrating Yes. I have a feeling there is something different about the way our Access systems are set up but as a different approach, perhaps it's worth trying to copy the three columns that I see /in the visual designer/: Column 1 -------- Field: Email1Address Table: DD2 LIST Show: checked Criteria: Is Not Null Column 2 -------- Field: MergedName: [DD2 LIST].[FirstName] & " " & [DD2 LIST].[LastName] Show: checked Column 3 -------- Field: Trim([DD2 LIST].[Email1Address]) Show: not checked Criteria: "" (FWIW I do not think it makes any difference here but I tend to use single quotes rather than double quotes to surround strings in Access queries, i.e. Column 1 -------- Field: Email1Address Table: DD2 LIST Show: checked Criteria: Is Not Null Column 2 -------- Field: MergedName: [DD2 LIST].[FirstName] & ' ' & [DD2 LIST].[LastName] Show: checked Column 3 -------- Field: Trim([DD2 LIST].[Email1Address]) Show: not checked Criteria: '' The table is called "DD2 LIST"? (not, e.g. "DD2_LIST" ?) Peter Jamieson http://tips.pjmsn.me.uk Susan May wrote: Peter, it gives me DD2 - Enter Parameter Value and I click ok, and the blank emails are still in there, but when I looked in the design view, under merged name, this is what it shows. MergedName: [DD2 LIST].[FirstName] & " " & [DD2 I tried to finish the string and it tells me the expression you entered contains invalid syntax. You may have entered an operand without an operator MergedName: [DD2 LIST].[FirstName] & " " & [DD2 LIST].[LastName] LIST].[LastName] This above is what I have now and the query will not run. This is so frustrating - you'd think this should be so simple in Access and all you should have to do is say is not null under Email1Address, but it's not. Help! "Peter Jamieson" wrote: Hi Susan, 1. In that SQL pane, can you first try replacing what you have with SELECT [DD2 LIST].Email1Address, [DD2 LIST].[FirstName] & " " & [DD2 LIST].[LastName] AS MergedName FROM [DD2 LIST] WHERE [DD2 LIST].Email1Address IS NOT NULL AND trim([DD2 LIST].Email1Address) "" which is the query I think you should be running, so a. try executing that b. if you want, see how that "translates" to the settings in the visual designer (at this point you may find it easier to work in the SQL pane) 2. If that does not function, try SELECT [DD2 LIST].Email1Address, [DD2 LIST].[FirstName] & " " & [DD2 LIST].[LastName] AS MergedName, Len(Trim("Email1Address")), Asc(Mid(Trim("Email1Address"),1,1)) FROM [DD2 LIST] which may give some further info. Peter Jamieson http://tips.pjmsn.me.uk Susan May wrote: Here Peter: SELECT [DD2 LIST].Email1Address, [FirstName] & " " & [LastName] AS MergedName FROM [DD2 LIST] WHERE ((([DD2 LIST].Email1Address)=Len(Trim("Email1Address")) Or ([DD2 LIST].Email1Address)=Asc(Mid(Trim("Email1Address"),1,1) ))); "Peter Jamieson" wrote: Susan, What am I doing wrong? I don't know - not necessarily anything. NB I was intending these test queries to be executed entirely within Access - is that what you are doing? Can you please a. look at the SQL for your query (if you haven't done this before, try right-clicking in the title bar of the query designer and selecting the SQL option) and copy/paste a copy here? b. tell us exactly what the error message says? Peter Jamieson http://tips.pjmsn.me.uk Susan May wrote: Peter when I do this WHERE mystring is not null AND trim(mystring) '' I get an error message about the string 2048 characters so it doesn't work. I do this - len(trim(Email1Address)) asc(mid(trim(Email1Address),1,1)) and no records appear. What am I doing wrong? Susan "Peter Jamieson" wrote: 1. FWIW you can probably reduce the criteria I suggested to WHERE mystring is not null AND trim(mystring) '' since the other condition should be taken care of by the trim() condition. 2. I think the next thing to do is check what's actually in that field in some of the records that should be eliminated. (I tend to work directly on the SQL, but if you are more familiar with the graphical designer I'd try to stick with that). 3. e.g. add a few columns to your query such as len(trim(Email1Address)) asc(mid(trim(Email1Address),1,1)) If the address looks blank but len(trim(Email1Address)) is 0 then the field probably contains invisible non-space characters. The asc() function should tell us what the first character is. You can use asc(mid(trim(Email1Address),2,1)) etc. to look at the other characters. Peter Jamieson http://tips.pjmsn.me.uk Susan May wrote: Peter: I tried this WHERE mystring is not null AND mystring '' AND trim(mystring) '' with mystring name as Email1Address, and it created 3 different columns with the criteria "is not null"; "; and Trim(Emai1lAddress) with criteria ", and I'm still getting blank emails. What did I do wrong? "Peter Jamieson" wrote: Also, when I set up my query, I selected the criteria for the email address field "is not null", and there are still some blank records with no emails. Different software packages can treat "null", "a string set to ''", and "a string containing white space differently, and may also treat variable-length and fixed-length data differently in this respect. As far as Access is concerned, in some cases if you enter "white space", Access leaves the field value as "null". However, if you set a text value to '' in code, it isn't regarded as null but will otherwise look no different to the user in many cases. So generally speaking, you have to test a string for both null and blank. to detect that in the SQL in Access you need something like WHERE mystring is not null AND mystring '' If the string could contain one or more spaces, you would probably need to add another condition, e.g. WHERE mystring is not null AND mystring '' AND trim(mystring) '' but if mystring could contain other types of "whitespace" such as tabs, non-breaking spaces, I think you would need to ask an Access person. You might be able to use LIKE with a pattern to do it. Peter Jamieson http://tips.pjmsn.me.uk Susan May wrote: Doug, I want to take a list of emails I have in a query and individually send them out so it looks like I'm sending them individually, but I'm actually sending them using a Word email merge because what I'm sending them are articles that we publish about the economy and these are potential recruits that have contacted us for more information but have not joined our firm yet. I don't want to copy and paste in bcc field in Outlook because that would make me look like a blaster and I don't want our server to be black listed. We have the rights to these emails so I was told by doing an email merge in Word, I can set it up so it would go out to each one individually and not alert the internet providers. Also, when I set up my query, I selected the criteria for the email address field "is not null", and there are still some blank records with no emails. Why is that happening? There are not many, but is there a better syntax to use to get rid of the records that do not have email addresses? Many thanks. PS Lately, when I've been notified of a response from my questions, the link does not take me to the page where the question is. It is blank - do you know why this is happening? Susan "Doug Robbins - Word MVP on news.microsof" wrote: Do you mean rather than their email address? -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP, originally posted via msnews.microsoft.com "Susan May" wrote in message ... I have a list of email address in an 2007 Access query. I would like to individually email merge articles I have written in MS Word and send them thru MS Outlook individually so they are professional sent with their first and last name on to To: field. How can this be done successfully? Many thanks for your help |
|
#15
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Email Merge in Word
Yes, field name is DD2 List. I typed exactly what you told me to do in the
three columns, but when I ran the query I am stil coming up with records that have blank email address. What's wrong with this damn program? "Peter Jamieson" wrote: This is so frustrating Yes. I have a feeling there is something different about the way our Access systems are set up but as a different approach, perhaps it's worth trying to copy the three columns that I see /in the visual designer/: Column 1 -------- Field: Email1Address Table: DD2 LIST Show: checked Criteria: Is Not Null Column 2 -------- Field: MergedName: [DD2 LIST].[FirstName] & " " & [DD2 LIST].[LastName] Show: checked Column 3 -------- Field: Trim([DD2 LIST].[Email1Address]) Show: not checked Criteria: "" (FWIW I do not think it makes any difference here but I tend to use single quotes rather than double quotes to surround strings in Access queries, i.e. Column 1 -------- Field: Email1Address Table: DD2 LIST Show: checked Criteria: Is Not Null Column 2 -------- Field: MergedName: [DD2 LIST].[FirstName] & ' ' & [DD2 LIST].[LastName] Show: checked Column 3 -------- Field: Trim([DD2 LIST].[Email1Address]) Show: not checked Criteria: '' The table is called "DD2 LIST"? (not, e.g. "DD2_LIST" ?) Peter Jamieson http://tips.pjmsn.me.uk Susan May wrote: Peter, it gives me DD2 - Enter Parameter Value and I click ok, and the blank emails are still in there, but when I looked in the design view, under merged name, this is what it shows. MergedName: [DD2 LIST].[FirstName] & " " & [DD2 I tried to finish the string and it tells me the expression you entered contains invalid syntax. You may have entered an operand without an operator MergedName: [DD2 LIST].[FirstName] & " " & [DD2 LIST].[LastName] LIST].[LastName] This above is what I have now and the query will not run. This is so frustrating - you'd think this should be so simple in Access and all you should have to do is say is not null under Email1Address, but it's not. Help! "Peter Jamieson" wrote: Hi Susan, 1. In that SQL pane, can you first try replacing what you have with SELECT [DD2 LIST].Email1Address, [DD2 LIST].[FirstName] & " " & [DD2 LIST].[LastName] AS MergedName FROM [DD2 LIST] WHERE [DD2 LIST].Email1Address IS NOT NULL AND trim([DD2 LIST].Email1Address) "" which is the query I think you should be running, so a. try executing that b. if you want, see how that "translates" to the settings in the visual designer (at this point you may find it easier to work in the SQL pane) 2. If that does not function, try SELECT [DD2 LIST].Email1Address, [DD2 LIST].[FirstName] & " " & [DD2 LIST].[LastName] AS MergedName, Len(Trim("Email1Address")), Asc(Mid(Trim("Email1Address"),1,1)) FROM [DD2 LIST] which may give some further info. Peter Jamieson http://tips.pjmsn.me.uk Susan May wrote: Here Peter: SELECT [DD2 LIST].Email1Address, [FirstName] & " " & [LastName] AS MergedName FROM [DD2 LIST] WHERE ((([DD2 LIST].Email1Address)=Len(Trim("Email1Address")) Or ([DD2 LIST].Email1Address)=Asc(Mid(Trim("Email1Address"),1,1) ))); "Peter Jamieson" wrote: Susan, What am I doing wrong? I don't know - not necessarily anything. NB I was intending these test queries to be executed entirely within Access - is that what you are doing? Can you please a. look at the SQL for your query (if you haven't done this before, try right-clicking in the title bar of the query designer and selecting the SQL option) and copy/paste a copy here? b. tell us exactly what the error message says? Peter Jamieson http://tips.pjmsn.me.uk Susan May wrote: Peter when I do this WHERE mystring is not null AND trim(mystring) '' I get an error message about the string 2048 characters so it doesn't work. I do this - len(trim(Email1Address)) asc(mid(trim(Email1Address),1,1)) and no records appear. What am I doing wrong? Susan "Peter Jamieson" wrote: 1. FWIW you can probably reduce the criteria I suggested to WHERE mystring is not null AND trim(mystring) '' since the other condition should be taken care of by the trim() condition. 2. I think the next thing to do is check what's actually in that field in some of the records that should be eliminated. (I tend to work directly on the SQL, but if you are more familiar with the graphical designer I'd try to stick with that). 3. e.g. add a few columns to your query such as len(trim(Email1Address)) asc(mid(trim(Email1Address),1,1)) If the address looks blank but len(trim(Email1Address)) is 0 then the field probably contains invisible non-space characters. The asc() function should tell us what the first character is. You can use asc(mid(trim(Email1Address),2,1)) etc. to look at the other characters. Peter Jamieson http://tips.pjmsn.me.uk Susan May wrote: Peter: I tried this WHERE mystring is not null AND mystring '' AND trim(mystring) '' with mystring name as Email1Address, and it created 3 different columns with the criteria "is not null"; "; and Trim(Emai1lAddress) with criteria ", and I'm still getting blank emails. What did I do wrong? "Peter Jamieson" wrote: Also, when I set up my query, I selected the criteria for the email address field "is not null", and there are still some blank records with no emails. Different software packages can treat "null", "a string set to ''", and "a string containing white space differently, and may also treat variable-length and fixed-length data differently in this respect. As far as Access is concerned, in some cases if you enter "white space", Access leaves the field value as "null". However, if you set a text value to '' in code, it isn't regarded as null but will otherwise look no different to the user in many cases. So generally speaking, you have to test a string for both null and blank. to detect that in the SQL in Access you need something like WHERE mystring is not null AND mystring '' If the string could contain one or more spaces, you would probably need to add another condition, e.g. WHERE mystring is not null AND mystring '' AND trim(mystring) '' but if mystring could contain other types of "whitespace" such as tabs, non-breaking spaces, I think you would need to ask an Access person. You might be able to use LIKE with a pattern to do it. Peter Jamieson http://tips.pjmsn.me.uk Susan May wrote: Doug, I want to take a list of emails I have in a query and individually send them out so it looks like I'm sending them individually, but I'm actually sending them using a Word email merge because what I'm sending them are articles that we publish about the economy and these are potential recruits that have contacted us for more information but have not joined our firm yet. I don't want to copy and paste in bcc field in Outlook because that would make me look like a blaster and I don't want our server to be black listed. We have the rights to these emails so I was told by doing an email merge in Word, I can set it up so it would go out to each one individually and not alert the internet providers. Also, when I set up my query, I selected the criteria for the email address field "is not null", and there are still some blank records with no emails. Why is that happening? There are not many, but is there a better syntax to use to get rid of the records that do not have email addresses? Many thanks. PS Lately, when I've been notified of a response from my questions, the link does not take me to the page where the question is. It is blank - do you know why this is happening? Susan "Doug Robbins - Word MVP on news.microsof" wrote: Do you mean rather than their email address? -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP, originally posted via msnews.microsoft.com "Susan May" wrote in message ... I have a list of email address in an 2007 Access query. I would like to individually email merge articles I have written in MS Word and send them thru MS Outlook individually so they are professional sent with their first and last name on to To: field. How can this be done successfully? Many thanks for your help |
|
#16
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Email Merge in Word
three columns, but when I ran the query I am stil coming up with
records that have blank email address. OK, but at least it ran :-) Can you do me a favour and look at the SQL pane for that query, and copy/paste the code in here? I'd just like to compare it with what we tried before. Let's try the other query now - I have modified it a bit: The SQL version: SELECT [DD2 LIST].Email1Address, [DD2 LIST].[FirstName] & ' ' & [DD2 LIST].[LastName] AS MergedName, Len(Trim(Email1Address)) AS Expr1, Asc(Mid(Trim(Email1Address),1,1)) AS Expr2 FROM [DD2 LIST] WHERE [DD2 LIST].Email1Address IS NOT NULL AND trim([DD2 LIST].Email1Address) '' The query designer version: Column 1 -------- Field: Email1Address Table: DD2 LIST Show: checked Criteria: Is Not Null Column 2 -------- Field: MergedName: [DD2 LIST].[FirstName] & ' ' & [DD2 LIST].[LastName] Show: checked Column 3 -------- Field: Expr1: Len(Trim(Email1Address)) Show: checked Column 4 -------- Field: Expr2: Asc(Mid(Trim(Email1Address),1,1)) Show: checked Column 2 -------- Field: Trim([DD2 LIST].[Email1Address]) Show: checked Criteria: '' (All the quotes in there are single quotes, but you can probably use double quotes if you prefer). With any luck, this should list the same records that you had before, but tell us something about what is in Email1Address BTW I may not be able to post back until tomorrow (UK time) now... Peter Jamieson http://tips.pjmsn.me.uk Susan May wrote: Yes, field name is DD2 List. I typed exactly what you told me to do in the three columns, but when I ran the query I am stil coming up with records that have blank email address. What's wrong with this damn program? "Peter Jamieson" wrote: This is so frustrating Yes. I have a feeling there is something different about the way our Access systems are set up but as a different approach, perhaps it's worth trying to copy the three columns that I see /in the visual designer/: Column 1 -------- Field: Email1Address Table: DD2 LIST Show: checked Criteria: Is Not Null Column 2 -------- Field: MergedName: [DD2 LIST].[FirstName] & " " & [DD2 LIST].[LastName] Show: checked Column 3 -------- Field: Trim([DD2 LIST].[Email1Address]) Show: not checked Criteria: "" (FWIW I do not think it makes any difference here but I tend to use single quotes rather than double quotes to surround strings in Access queries, i.e. Column 1 -------- Field: Email1Address Table: DD2 LIST Show: checked Criteria: Is Not Null Column 2 -------- Field: MergedName: [DD2 LIST].[FirstName] & ' ' & [DD2 LIST].[LastName] Show: checked Column 3 -------- Field: Trim([DD2 LIST].[Email1Address]) Show: not checked Criteria: '' The table is called "DD2 LIST"? (not, e.g. "DD2_LIST" ?) Peter Jamieson http://tips.pjmsn.me.uk Susan May wrote: Peter, it gives me DD2 - Enter Parameter Value and I click ok, and the blank emails are still in there, but when I looked in the design view, under merged name, this is what it shows. MergedName: [DD2 LIST].[FirstName] & " " & [DD2 I tried to finish the string and it tells me the expression you entered contains invalid syntax. You may have entered an operand without an operator MergedName: [DD2 LIST].[FirstName] & " " & [DD2 LIST].[LastName] LIST].[LastName] This above is what I have now and the query will not run. This is so frustrating - you'd think this should be so simple in Access and all you should have to do is say is not null under Email1Address, but it's not. Help! "Peter Jamieson" wrote: Hi Susan, 1. In that SQL pane, can you first try replacing what you have with SELECT [DD2 LIST].Email1Address, [DD2 LIST].[FirstName] & " " & [DD2 LIST].[LastName] AS MergedName FROM [DD2 LIST] WHERE [DD2 LIST].Email1Address IS NOT NULL AND trim([DD2 LIST].Email1Address) "" which is the query I think you should be running, so a. try executing that b. if you want, see how that "translates" to the settings in the visual designer (at this point you may find it easier to work in the SQL pane) 2. If that does not function, try SELECT [DD2 LIST].Email1Address, [DD2 LIST].[FirstName] & " " & [DD2 LIST].[LastName] AS MergedName, Len(Trim("Email1Address")), Asc(Mid(Trim("Email1Address"),1,1)) FROM [DD2 LIST] which may give some further info. Peter Jamieson http://tips.pjmsn.me.uk Susan May wrote: Here Peter: SELECT [DD2 LIST].Email1Address, [FirstName] & " " & [LastName] AS MergedName FROM [DD2 LIST] WHERE ((([DD2 LIST].Email1Address)=Len(Trim("Email1Address")) Or ([DD2 LIST].Email1Address)=Asc(Mid(Trim("Email1Address"),1,1) ))); "Peter Jamieson" wrote: Susan, What am I doing wrong? I don't know - not necessarily anything. NB I was intending these test queries to be executed entirely within Access - is that what you are doing? Can you please a. look at the SQL for your query (if you haven't done this before, try right-clicking in the title bar of the query designer and selecting the SQL option) and copy/paste a copy here? b. tell us exactly what the error message says? Peter Jamieson http://tips.pjmsn.me.uk Susan May wrote: Peter when I do this WHERE mystring is not null AND trim(mystring) '' I get an error message about the string 2048 characters so it doesn't work. I do this - len(trim(Email1Address)) asc(mid(trim(Email1Address),1,1)) and no records appear. What am I doing wrong? Susan "Peter Jamieson" wrote: 1. FWIW you can probably reduce the criteria I suggested to WHERE mystring is not null AND trim(mystring) '' since the other condition should be taken care of by the trim() condition. 2. I think the next thing to do is check what's actually in that field in some of the records that should be eliminated. (I tend to work directly on the SQL, but if you are more familiar with the graphical designer I'd try to stick with that). 3. e.g. add a few columns to your query such as len(trim(Email1Address)) asc(mid(trim(Email1Address),1,1)) If the address looks blank but len(trim(Email1Address)) is 0 then the field probably contains invisible non-space characters. The asc() function should tell us what the first character is. You can use asc(mid(trim(Email1Address),2,1)) etc. to look at the other characters. Peter Jamieson http://tips.pjmsn.me.uk Susan May wrote: Peter: I tried this WHERE mystring is not null AND mystring '' AND trim(mystring) '' with mystring name as Email1Address, and it created 3 different columns with the criteria "is not null"; "; and Trim(Emai1lAddress) with criteria ", and I'm still getting blank emails. What did I do wrong? "Peter Jamieson" wrote: Also, when I set up my query, I selected the criteria for the email address field "is not null", and there are still some blank records with no emails. Different software packages can treat "null", "a string set to ''", and "a string containing white space differently, and may also treat variable-length and fixed-length data differently in this respect. As far as Access is concerned, in some cases if you enter "white space", Access leaves the field value as "null". However, if you set a text value to '' in code, it isn't regarded as null but will otherwise look no different to the user in many cases. So generally speaking, you have to test a string for both null and blank. to detect that in the SQL in Access you need something like WHERE mystring is not null AND mystring '' If the string could contain one or more spaces, you would probably need to add another condition, e.g. WHERE mystring is not null AND mystring '' AND trim(mystring) '' but if mystring could contain other types of "whitespace" such as tabs, non-breaking spaces, I think you would need to ask an Access person. You might be able to use LIKE with a pattern to do it. Peter Jamieson http://tips.pjmsn.me.uk Susan May wrote: Doug, I want to take a list of emails I have in a query and individually send them out so it looks like I'm sending them individually, but I'm actually sending them using a Word email merge because what I'm sending them are articles that we publish about the economy and these are potential recruits that have contacted us for more information but have not joined our firm yet. I don't want to copy and paste in bcc field in Outlook because that would make me look like a blaster and I don't want our server to be black listed. We have the rights to these emails so I was told by doing an email merge in Word, I can set it up so it would go out to each one individually and not alert the internet providers. Also, when I set up my query, I selected the criteria for the email address field "is not null", and there are still some blank records with no emails. Why is that happening? There are not many, but is there a better syntax to use to get rid of the records that do not have email addresses? Many thanks. PS Lately, when I've been notified of a response from my questions, the link does not take me to the page where the question is. It is blank - do you know why this is happening? Susan "Doug Robbins - Word MVP on news.microsof" wrote: Do you mean rather than their email address? -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP, originally posted via msnews.microsoft.com "Susan May" wrote in message ... I have a list of email address in an 2007 Access query. I would like to individually email merge articles I have written in MS Word and send them thru MS Outlook individually so they are professional sent with their first and last name on to To: field. How can this be done successfully? Many thanks for your help |
|
#17
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Email Merge in Word
That worked Peter. Thanks so much for your help!
"Peter Jamieson" wrote: three columns, but when I ran the query I am stil coming up with records that have blank email address. OK, but at least it ran :-) Can you do me a favour and look at the SQL pane for that query, and copy/paste the code in here? I'd just like to compare it with what we tried before. Let's try the other query now - I have modified it a bit: The SQL version: SELECT [DD2 LIST].Email1Address, [DD2 LIST].[FirstName] & ' ' & [DD2 LIST].[LastName] AS MergedName, Len(Trim(Email1Address)) AS Expr1, Asc(Mid(Trim(Email1Address),1,1)) AS Expr2 FROM [DD2 LIST] WHERE [DD2 LIST].Email1Address IS NOT NULL AND trim([DD2 LIST].Email1Address) '' The query designer version: Column 1 -------- Field: Email1Address Table: DD2 LIST Show: checked Criteria: Is Not Null Column 2 -------- Field: MergedName: [DD2 LIST].[FirstName] & ' ' & [DD2 LIST].[LastName] Show: checked Column 3 -------- Field: Expr1: Len(Trim(Email1Address)) Show: checked Column 4 -------- Field: Expr2: Asc(Mid(Trim(Email1Address),1,1)) Show: checked Column 2 -------- Field: Trim([DD2 LIST].[Email1Address]) Show: checked Criteria: '' (All the quotes in there are single quotes, but you can probably use double quotes if you prefer). With any luck, this should list the same records that you had before, but tell us something about what is in Email1Address BTW I may not be able to post back until tomorrow (UK time) now... Peter Jamieson http://tips.pjmsn.me.uk Susan May wrote: Yes, field name is DD2 List. I typed exactly what you told me to do in the three columns, but when I ran the query I am stil coming up with records that have blank email address. What's wrong with this damn program? "Peter Jamieson" wrote: This is so frustrating Yes. I have a feeling there is something different about the way our Access systems are set up but as a different approach, perhaps it's worth trying to copy the three columns that I see /in the visual designer/: Column 1 -------- Field: Email1Address Table: DD2 LIST Show: checked Criteria: Is Not Null Column 2 -------- Field: MergedName: [DD2 LIST].[FirstName] & " " & [DD2 LIST].[LastName] Show: checked Column 3 -------- Field: Trim([DD2 LIST].[Email1Address]) Show: not checked Criteria: "" (FWIW I do not think it makes any difference here but I tend to use single quotes rather than double quotes to surround strings in Access queries, i.e. Column 1 -------- Field: Email1Address Table: DD2 LIST Show: checked Criteria: Is Not Null Column 2 -------- Field: MergedName: [DD2 LIST].[FirstName] & ' ' & [DD2 LIST].[LastName] Show: checked Column 3 -------- Field: Trim([DD2 LIST].[Email1Address]) Show: not checked Criteria: '' The table is called "DD2 LIST"? (not, e.g. "DD2_LIST" ?) Peter Jamieson http://tips.pjmsn.me.uk Susan May wrote: Peter, it gives me DD2 - Enter Parameter Value and I click ok, and the blank emails are still in there, but when I looked in the design view, under merged name, this is what it shows. MergedName: [DD2 LIST].[FirstName] & " " & [DD2 I tried to finish the string and it tells me the expression you entered contains invalid syntax. You may have entered an operand without an operator MergedName: [DD2 LIST].[FirstName] & " " & [DD2 LIST].[LastName] LIST].[LastName] This above is what I have now and the query will not run. This is so frustrating - you'd think this should be so simple in Access and all you should have to do is say is not null under Email1Address, but it's not. Help! "Peter Jamieson" wrote: Hi Susan, 1. In that SQL pane, can you first try replacing what you have with SELECT [DD2 LIST].Email1Address, [DD2 LIST].[FirstName] & " " & [DD2 LIST].[LastName] AS MergedName FROM [DD2 LIST] WHERE [DD2 LIST].Email1Address IS NOT NULL AND trim([DD2 LIST].Email1Address) "" which is the query I think you should be running, so a. try executing that b. if you want, see how that "translates" to the settings in the visual designer (at this point you may find it easier to work in the SQL pane) 2. If that does not function, try SELECT [DD2 LIST].Email1Address, [DD2 LIST].[FirstName] & " " & [DD2 LIST].[LastName] AS MergedName, Len(Trim("Email1Address")), Asc(Mid(Trim("Email1Address"),1,1)) FROM [DD2 LIST] which may give some further info. Peter Jamieson http://tips.pjmsn.me.uk Susan May wrote: Here Peter: SELECT [DD2 LIST].Email1Address, [FirstName] & " " & [LastName] AS MergedName FROM [DD2 LIST] WHERE ((([DD2 LIST].Email1Address)=Len(Trim("Email1Address")) Or ([DD2 LIST].Email1Address)=Asc(Mid(Trim("Email1Address"),1,1) ))); "Peter Jamieson" wrote: Susan, What am I doing wrong? I don't know - not necessarily anything. NB I was intending these test queries to be executed entirely within Access - is that what you are doing? Can you please a. look at the SQL for your query (if you haven't done this before, try right-clicking in the title bar of the query designer and selecting the SQL option) and copy/paste a copy here? b. tell us exactly what the error message says? Peter Jamieson http://tips.pjmsn.me.uk Susan May wrote: Peter when I do this WHERE mystring is not null AND trim(mystring) '' I get an error message about the string 2048 characters so it doesn't work. I do this - len(trim(Email1Address)) asc(mid(trim(Email1Address),1,1)) and no records appear. What am I doing wrong? Susan "Peter Jamieson" wrote: 1. FWIW you can probably reduce the criteria I suggested to WHERE mystring is not null AND trim(mystring) '' since the other condition should be taken care of by the trim() condition. 2. I think the next thing to do is check what's actually in that field in some of the records that should be eliminated. (I tend to work directly on the SQL, but if you are more familiar with the graphical designer I'd try to stick with that). 3. e.g. add a few columns to your query such as len(trim(Email1Address)) asc(mid(trim(Email1Address),1,1)) If the address looks blank but len(trim(Email1Address)) is 0 then the field probably contains invisible non-space characters. The asc() function should tell us what the first character is. You can use asc(mid(trim(Email1Address),2,1)) etc. to look at the other characters. Peter Jamieson http://tips.pjmsn.me.uk Susan May wrote: Peter: I tried this WHERE mystring is not null AND mystring '' AND trim(mystring) '' with mystring name as Email1Address, and it created 3 different columns with the criteria "is not null"; "; and Trim(Emai1lAddress) with criteria ", and I'm still getting blank emails. What did I do wrong? "Peter Jamieson" wrote: Also, when I set up my query, I selected the criteria for the email address field "is not null", and there are still some blank records with no emails. Different software packages can treat "null", "a string set to ''", and "a string containing white space differently, and may also treat variable-length and fixed-length data differently in this respect. As far as Access is concerned, in some cases if you enter "white space", Access leaves the field value as "null". However, if you set a text value to '' in code, it isn't regarded as null but will otherwise look no different to the user in many cases. So generally speaking, you have to test a string for both null and blank. to detect that in the SQL in Access you need something like WHERE mystring is not null AND mystring '' If the string could contain one or more spaces, you would probably need to add another condition, e.g. WHERE mystring is not null AND mystring '' AND trim(mystring) '' but if mystring could contain other types of "whitespace" such as tabs, non-breaking spaces, I think you would need to ask an Access person. You might be able to use LIKE with a pattern to do it. Peter Jamieson http://tips.pjmsn.me.uk Susan May wrote: Doug, I want to take a list of emails I have in a query and individually send them out so it looks like I'm sending them individually, but I'm actually sending them using a Word email merge because what I'm sending them are articles that we publish about the economy and these are potential recruits that have contacted us for more information but have not joined our firm yet. I don't want to copy and paste in bcc field in Outlook because that would make me look like a blaster and I don't want our server to be black listed. We have the rights to these emails so I was told by doing an email merge in Word, I can set it up so it would go out to each one individually and not alert the internet providers. |
|
#18
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Email Merge in Word
What did you have for the criteria? It should be Is Not Null. The use of
the Trim() function would remove any spaces that might be in an otherwise empty record and the Is Not Null would prevent such a record from being displayed so that only records that contained characters other than spaces would be displayed. -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP, originally posted via msnews.microsoft.com "Susan May" wrote in message ... Doug - no records appear when I enter in your string. What am I doing wrong? Susan "Doug Robbins - Word MVP on news.microsof" wrote: Do you mean rather than their email address? -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP, originally posted via msnews.microsoft.com "Susan May" wrote in message ... I have a list of email address in an 2007 Access query. I would like to individually email merge articles I have written in MS Word and send them thru MS Outlook individually so they are professional sent with their first and last name on to To: field. How can this be done successfully? Many thanks for your help |
|
#19
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Email Merge in Word
Did you manage to work out from that what the character(s) causing the
problem was/were? Peter Jamieson http://tips.pjmsn.me.uk Susan May wrote: That worked Peter. Thanks so much for your help! "Peter Jamieson" wrote: three columns, but when I ran the query I am stil coming up with records that have blank email address. OK, but at least it ran :-) Can you do me a favour and look at the SQL pane for that query, and copy/paste the code in here? I'd just like to compare it with what we tried before. Let's try the other query now - I have modified it a bit: The SQL version: SELECT [DD2 LIST].Email1Address, [DD2 LIST].[FirstName] & ' ' & [DD2 LIST].[LastName] AS MergedName, Len(Trim(Email1Address)) AS Expr1, Asc(Mid(Trim(Email1Address),1,1)) AS Expr2 FROM [DD2 LIST] WHERE [DD2 LIST].Email1Address IS NOT NULL AND trim([DD2 LIST].Email1Address) '' The query designer version: Column 1 -------- Field: Email1Address Table: DD2 LIST Show: checked Criteria: Is Not Null Column 2 -------- Field: MergedName: [DD2 LIST].[FirstName] & ' ' & [DD2 LIST].[LastName] Show: checked Column 3 -------- Field: Expr1: Len(Trim(Email1Address)) Show: checked Column 4 -------- Field: Expr2: Asc(Mid(Trim(Email1Address),1,1)) Show: checked Column 2 -------- Field: Trim([DD2 LIST].[Email1Address]) Show: checked Criteria: '' (All the quotes in there are single quotes, but you can probably use double quotes if you prefer). With any luck, this should list the same records that you had before, but tell us something about what is in Email1Address BTW I may not be able to post back until tomorrow (UK time) now... Peter Jamieson http://tips.pjmsn.me.uk Susan May wrote: Yes, field name is DD2 List. I typed exactly what you told me to do in the three columns, but when I ran the query I am stil coming up with records that have blank email address. What's wrong with this damn program? "Peter Jamieson" wrote: This is so frustrating Yes. I have a feeling there is something different about the way our Access systems are set up but as a different approach, perhaps it's worth trying to copy the three columns that I see /in the visual designer/: Column 1 -------- Field: Email1Address Table: DD2 LIST Show: checked Criteria: Is Not Null Column 2 -------- Field: MergedName: [DD2 LIST].[FirstName] & " " & [DD2 LIST].[LastName] Show: checked Column 3 -------- Field: Trim([DD2 LIST].[Email1Address]) Show: not checked Criteria: "" (FWIW I do not think it makes any difference here but I tend to use single quotes rather than double quotes to surround strings in Access queries, i.e. Column 1 -------- Field: Email1Address Table: DD2 LIST Show: checked Criteria: Is Not Null Column 2 -------- Field: MergedName: [DD2 LIST].[FirstName] & ' ' & [DD2 LIST].[LastName] Show: checked Column 3 -------- Field: Trim([DD2 LIST].[Email1Address]) Show: not checked Criteria: '' The table is called "DD2 LIST"? (not, e.g. "DD2_LIST" ?) Peter Jamieson http://tips.pjmsn.me.uk Susan May wrote: Peter, it gives me DD2 - Enter Parameter Value and I click ok, and the blank emails are still in there, but when I looked in the design view, under merged name, this is what it shows. MergedName: [DD2 LIST].[FirstName] & " " & [DD2 I tried to finish the string and it tells me the expression you entered contains invalid syntax. You may have entered an operand without an operator MergedName: [DD2 LIST].[FirstName] & " " & [DD2 LIST].[LastName] LIST].[LastName] This above is what I have now and the query will not run. This is so frustrating - you'd think this should be so simple in Access and all you should have to do is say is not null under Email1Address, but it's not. Help! "Peter Jamieson" wrote: Hi Susan, 1. In that SQL pane, can you first try replacing what you have with SELECT [DD2 LIST].Email1Address, [DD2 LIST].[FirstName] & " " & [DD2 LIST].[LastName] AS MergedName FROM [DD2 LIST] WHERE [DD2 LIST].Email1Address IS NOT NULL AND trim([DD2 LIST].Email1Address) "" which is the query I think you should be running, so a. try executing that b. if you want, see how that "translates" to the settings in the visual designer (at this point you may find it easier to work in the SQL pane) 2. If that does not function, try SELECT [DD2 LIST].Email1Address, [DD2 LIST].[FirstName] & " " & [DD2 LIST].[LastName] AS MergedName, Len(Trim("Email1Address")), Asc(Mid(Trim("Email1Address"),1,1)) FROM [DD2 LIST] which may give some further info. Peter Jamieson http://tips.pjmsn.me.uk Susan May wrote: Here Peter: SELECT [DD2 LIST].Email1Address, [FirstName] & " " & [LastName] AS MergedName FROM [DD2 LIST] WHERE ((([DD2 LIST].Email1Address)=Len(Trim("Email1Address")) Or ([DD2 LIST].Email1Address)=Asc(Mid(Trim("Email1Address"),1,1) ))); "Peter Jamieson" wrote: Susan, What am I doing wrong? I don't know - not necessarily anything. NB I was intending these test queries to be executed entirely within Access - is that what you are doing? Can you please a. look at the SQL for your query (if you haven't done this before, try right-clicking in the title bar of the query designer and selecting the SQL option) and copy/paste a copy here? b. tell us exactly what the error message says? Peter Jamieson http://tips.pjmsn.me.uk Susan May wrote: Peter when I do this WHERE mystring is not null AND trim(mystring) '' I get an error message about the string 2048 characters so it doesn't work. I do this - len(trim(Email1Address)) asc(mid(trim(Email1Address),1,1)) and no records appear. What am I doing wrong? Susan "Peter Jamieson" wrote: 1. FWIW you can probably reduce the criteria I suggested to WHERE mystring is not null AND trim(mystring) '' since the other condition should be taken care of by the trim() condition. 2. I think the next thing to do is check what's actually in that field in some of the records that should be eliminated. (I tend to work directly on the SQL, but if you are more familiar with the graphical designer I'd try to stick with that). 3. e.g. add a few columns to your query such as len(trim(Email1Address)) asc(mid(trim(Email1Address),1,1)) If the address looks blank but len(trim(Email1Address)) is 0 then the field probably contains invisible non-space characters. The asc() function should tell us what the first character is. You can use asc(mid(trim(Email1Address),2,1)) etc. to look at the other characters. Peter Jamieson http://tips.pjmsn.me.uk Susan May wrote: Peter: I tried this WHERE mystring is not null AND mystring '' AND trim(mystring) '' with mystring name as Email1Address, and it created 3 different columns with the criteria "is not null"; "; and Trim(Emai1lAddress) with criteria ", and I'm still getting blank emails. What did I do wrong? "Peter Jamieson" wrote: Also, when I set up my query, I selected the criteria for the email address field "is not null", and there are still some blank records with no emails. Different software packages can treat "null", "a string set to ''", and "a string containing white space differently, and may also treat variable-length and fixed-length data differently in this respect. As far as Access is concerned, in some cases if you enter "white space", Access leaves the field value as "null". However, if you set a text value to '' in code, it isn't regarded as null but will otherwise look no different to the user in many cases. So generally speaking, you have to test a string for both null and blank. to detect that in the SQL in Access you need something like WHERE mystring is not null AND mystring '' If the string could contain one or more spaces, you would probably need to add another condition, e.g. WHERE mystring is not null AND mystring '' AND trim(mystring) '' but if mystring could contain other types of "whitespace" such as tabs, non-breaking spaces, I think you would need to ask an Access person. You might be able to use LIKE with a pattern to do it. Peter Jamieson http://tips.pjmsn.me.uk Susan May wrote: Doug, I want to take a list of emails I have in a query and individually send them out so it looks like I'm sending them individually, but I'm actually sending them using a Word email merge because what I'm sending them are articles that we publish about the economy and these are potential recruits that have contacted us for more information but have not joined our firm yet. I don't want to copy and paste in bcc field in Outlook because that would make me look like a blaster and I don't want our server to be black listed. We have the rights to these emails so I was told by doing an email merge in Word, I can set it up so it would go out to each one individually and not alert the internet providers. |
|
#20
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Email Merge in Word
Peter - I was copying and pasting from your comments into sql. And the error
messages I was receving didn't look like the text you had told me to put in each column. Then I figured out that when I cut and pasted your programming, the line carriage return () was also being copied into the sql. Once I deleted these, it worked perfectly. I felt so stupid, but learned a great lesson from you yesterday. Thanks for hanging in there for me. Without my Microsoft Tech questions, I don't know what I would do. You guys/gals have helped me tremendously with different issues as I get no support from my IT department here. Thanks so much again. Susan "Peter Jamieson" wrote: Did you manage to work out from that what the character(s) causing the problem was/were? Peter Jamieson http://tips.pjmsn.me.uk Susan May wrote: That worked Peter. Thanks so much for your help! "Peter Jamieson" wrote: three columns, but when I ran the query I am stil coming up with records that have blank email address. OK, but at least it ran :-) Can you do me a favour and look at the SQL pane for that query, and copy/paste the code in here? I'd just like to compare it with what we tried before. Let's try the other query now - I have modified it a bit: The SQL version: SELECT [DD2 LIST].Email1Address, [DD2 LIST].[FirstName] & ' ' & [DD2 LIST].[LastName] AS MergedName, Len(Trim(Email1Address)) AS Expr1, Asc(Mid(Trim(Email1Address),1,1)) AS Expr2 FROM [DD2 LIST] WHERE [DD2 LIST].Email1Address IS NOT NULL AND trim([DD2 LIST].Email1Address) '' The query designer version: Column 1 -------- Field: Email1Address Table: DD2 LIST Show: checked Criteria: Is Not Null Column 2 -------- Field: MergedName: [DD2 LIST].[FirstName] & ' ' & [DD2 LIST].[LastName] Show: checked Column 3 -------- Field: Expr1: Len(Trim(Email1Address)) Show: checked Column 4 -------- Field: Expr2: Asc(Mid(Trim(Email1Address),1,1)) Show: checked Column 2 -------- Field: Trim([DD2 LIST].[Email1Address]) Show: checked Criteria: '' (All the quotes in there are single quotes, but you can probably use double quotes if you prefer). With any luck, this should list the same records that you had before, but tell us something about what is in Email1Address BTW I may not be able to post back until tomorrow (UK time) now... Peter Jamieson http://tips.pjmsn.me.uk Susan May wrote: Yes, field name is DD2 List. I typed exactly what you told me to do in the three columns, but when I ran the query I am stil coming up with records that have blank email address. What's wrong with this damn program? "Peter Jamieson" wrote: This is so frustrating Yes. I have a feeling there is something different about the way our Access systems are set up but as a different approach, perhaps it's worth trying to copy the three columns that I see /in the visual designer/: Column 1 -------- Field: Email1Address Table: DD2 LIST Show: checked Criteria: Is Not Null Column 2 -------- Field: MergedName: [DD2 LIST].[FirstName] & " " & [DD2 LIST].[LastName] Show: checked Column 3 -------- Field: Trim([DD2 LIST].[Email1Address]) Show: not checked Criteria: "" (FWIW I do not think it makes any difference here but I tend to use single quotes rather than double quotes to surround strings in Access queries, i.e. Column 1 -------- Field: Email1Address Table: DD2 LIST Show: checked Criteria: Is Not Null Column 2 -------- Field: MergedName: [DD2 LIST].[FirstName] & ' ' & [DD2 LIST].[LastName] Show: checked Column 3 -------- Field: Trim([DD2 LIST].[Email1Address]) Show: not checked Criteria: '' The table is called "DD2 LIST"? (not, e.g. "DD2_LIST" ?) Peter Jamieson http://tips.pjmsn.me.uk Susan May wrote: Peter, it gives me DD2 - Enter Parameter Value and I click ok, and the blank emails are still in there, but when I looked in the design view, under merged name, this is what it shows. MergedName: [DD2 LIST].[FirstName] & " " & [DD2 I tried to finish the string and it tells me the expression you entered contains invalid syntax. You may have entered an operand without an operator MergedName: [DD2 LIST].[FirstName] & " " & [DD2 LIST].[LastName] LIST].[LastName] This above is what I have now and the query will not run. This is so frustrating - you'd think this should be so simple in Access and all you should have to do is say is not null under Email1Address, but it's not. Help! "Peter Jamieson" wrote: Hi Susan, 1. In that SQL pane, can you first try replacing what you have with SELECT [DD2 LIST].Email1Address, [DD2 LIST].[FirstName] & " " & [DD2 LIST].[LastName] AS MergedName FROM [DD2 LIST] WHERE [DD2 LIST].Email1Address IS NOT NULL AND trim([DD2 LIST].Email1Address) "" which is the query I think you should be running, so a. try executing that b. if you want, see how that "translates" to the settings in the visual designer (at this point you may find it easier to work in the SQL pane) 2. If that does not function, try SELECT [DD2 LIST].Email1Address, [DD2 LIST].[FirstName] & " " & [DD2 LIST].[LastName] AS MergedName, Len(Trim("Email1Address")), Asc(Mid(Trim("Email1Address"),1,1)) FROM [DD2 LIST] which may give some further info. Peter Jamieson http://tips.pjmsn.me.uk Susan May wrote: Here Peter: SELECT [DD2 LIST].Email1Address, [FirstName] & " " & [LastName] AS MergedName FROM [DD2 LIST] WHERE ((([DD2 LIST].Email1Address)=Len(Trim("Email1Address")) Or ([DD2 LIST].Email1Address)=Asc(Mid(Trim("Email1Address"),1,1) ))); "Peter Jamieson" wrote: Susan, What am I doing wrong? I don't know - not necessarily anything. NB I was intending these test queries to be executed entirely within Access - is that what you are doing? Can you please a. look at the SQL for your query (if you haven't done this before, try right-clicking in the title bar of the query designer and selecting the SQL option) and copy/paste a copy here? b. tell us exactly what the error message says? Peter Jamieson http://tips.pjmsn.me.uk Susan May wrote: Peter when I do this WHERE mystring is not null AND trim(mystring) '' I get an error message about the string 2048 characters so it doesn't work. I do this - len(trim(Email1Address)) asc(mid(trim(Email1Address),1,1)) and no records appear. What am I doing wrong? Susan "Peter Jamieson" wrote: 1. FWIW you can probably reduce the criteria I suggested to WHERE mystring is not null AND trim(mystring) '' since the other condition should be taken care of by the trim() condition. 2. I think the next thing to do is check what's actually in that field in some of the records that should be eliminated. (I tend to work directly on the SQL, but if you are more familiar with the graphical designer I'd try to stick with that). 3. e.g. add a few columns to your query such as len(trim(Email1Address)) asc(mid(trim(Email1Address),1,1)) If the address looks blank but len(trim(Email1Address)) is 0 then the field probably contains invisible non-space characters. The asc() function should tell us what the first character is. You can use asc(mid(trim(Email1Address),2,1)) etc. to look at the other characters. Peter Jamieson http://tips.pjmsn.me.uk Susan May wrote: Peter: I tried this WHERE mystring is not null AND mystring '' AND trim(mystring) '' with mystring name as Email1Address, and it created 3 different columns with the criteria "is not null"; "; and Trim(Emai1lAddress) with criteria ", and I'm still getting blank emails. What did I do wrong? "Peter Jamieson" wrote: Also, when I set up my query, I selected the criteria for the email address field "is not null", and there are still some blank records with no emails. Different software packages can treat "null", "a string set to ''", and "a string containing white space differently, and may also treat variable-length and fixed-length data differently in this respect. As far as Access is concerned, in some cases if you enter "white space", Access leaves the field value as "null". However, if you set a text value to '' in code, it isn't regarded as null but will otherwise look no different to the user in many cases. So generally speaking, you have to test a string for both null and blank. to detect that in the SQL in Access you need something like WHERE mystring is not null AND mystring '' If the string could contain one or more spaces, you would probably need to add another condition, e.g. WHERE mystring is not null AND mystring '' AND trim(mystring) '' but if mystring could contain other types of "whitespace" such as tabs, non-breaking spaces, I think you would need to ask an Access person. You might be able to use LIKE with a pattern to do it. Peter Jamieson http://tips.pjmsn.me.uk |
|
#21
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
|
Email Merge in Word
Hi Susan,
Thanks for getting back & glad it's now sorted. Peter Jamieson http://tips.pjmsn.me.uk Susan May wrote: Peter - I was copying and pasting from your comments into sql. And the error messages I was receving didn't look like the text you had told me to put in each column. Then I figured out that when I cut and pasted your programming, the line carriage return () was also being copied into the sql. Once I deleted these, it worked perfectly. I felt so stupid, but learned a great lesson from you yesterday. Thanks for hanging in there for me. Without my Microsoft Tech questions, I don't know what I would do. You guys/gals have helped me tremendously with different issues as I get no support from my IT department here. Thanks so much again. Susan "Peter Jamieson" wrote: Did you manage to work out from that what the character(s) causing the problem was/were? Peter Jamieson http://tips.pjmsn.me.uk Susan May wrote: That worked Peter. Thanks so much for your help! "Peter Jamieson" wrote: three columns, but when I ran the query I am stil coming up with records that have blank email address. OK, but at least it ran :-) Can you do me a favour and look at the SQL pane for that query, and copy/paste the code in here? I'd just like to compare it with what we tried before. Let's try the other query now - I have modified it a bit: The SQL version: SELECT [DD2 LIST].Email1Address, [DD2 LIST].[FirstName] & ' ' & [DD2 LIST].[LastName] AS MergedName, Len(Trim(Email1Address)) AS Expr1, Asc(Mid(Trim(Email1Address),1,1)) AS Expr2 FROM [DD2 LIST] WHERE [DD2 LIST].Email1Address IS NOT NULL AND trim([DD2 LIST].Email1Address) '' The query designer version: Column 1 -------- Field: Email1Address Table: DD2 LIST Show: checked Criteria: Is Not Null Column 2 -------- Field: MergedName: [DD2 LIST].[FirstName] & ' ' & [DD2 LIST].[LastName] Show: checked Column 3 -------- Field: Expr1: Len(Trim(Email1Address)) Show: checked Column 4 -------- Field: Expr2: Asc(Mid(Trim(Email1Address),1,1)) Show: checked Column 2 -------- Field: Trim([DD2 LIST].[Email1Address]) Show: checked Criteria: '' (All the quotes in there are single quotes, but you can probably use double quotes if you prefer). With any luck, this should list the same records that you had before, but tell us something about what is in Email1Address BTW I may not be able to post back until tomorrow (UK time) now... Peter Jamieson http://tips.pjmsn.me.uk Susan May wrote: Yes, field name is DD2 List. I typed exactly what you told me to do in the three columns, but when I ran the query I am stil coming up with records that have blank email address. What's wrong with this damn program? "Peter Jamieson" wrote: This is so frustrating Yes. I have a feeling there is something different about the way our Access systems are set up but as a different approach, perhaps it's worth trying to copy the three columns that I see /in the visual designer/: Column 1 -------- Field: Email1Address Table: DD2 LIST Show: checked Criteria: Is Not Null Column 2 -------- Field: MergedName: [DD2 LIST].[FirstName] & " " & [DD2 LIST].[LastName] Show: checked Column 3 -------- Field: Trim([DD2 LIST].[Email1Address]) Show: not checked Criteria: "" (FWIW I do not think it makes any difference here but I tend to use single quotes rather than double quotes to surround strings in Access queries, i.e. Column 1 -------- Field: Email1Address Table: DD2 LIST Show: checked Criteria: Is Not Null Column 2 -------- Field: MergedName: [DD2 LIST].[FirstName] & ' ' & [DD2 LIST].[LastName] Show: checked Column 3 -------- Field: Trim([DD2 LIST].[Email1Address]) Show: not checked Criteria: '' The table is called "DD2 LIST"? (not, e.g. "DD2_LIST" ?) Peter Jamieson http://tips.pjmsn.me.uk Susan May wrote: Peter, it gives me DD2 - Enter Parameter Value and I click ok, and the blank emails are still in there, but when I looked in the design view, under merged name, this is what it shows. MergedName: [DD2 LIST].[FirstName] & " " & [DD2 I tried to finish the string and it tells me the expression you entered contains invalid syntax. You may have entered an operand without an operator MergedName: [DD2 LIST].[FirstName] & " " & [DD2 LIST].[LastName] LIST].[LastName] This above is what I have now and the query will not run. This is so frustrating - you'd think this should be so simple in Access and all you should have to do is say is not null under Email1Address, but it's not. Help! "Peter Jamieson" wrote: Hi Susan, 1. In that SQL pane, can you first try replacing what you have with SELECT [DD2 LIST].Email1Address, [DD2 LIST].[FirstName] & " " & [DD2 LIST].[LastName] AS MergedName FROM [DD2 LIST] WHERE [DD2 LIST].Email1Address IS NOT NULL AND trim([DD2 LIST].Email1Address) "" which is the query I think you should be running, so a. try executing that b. if you want, see how that "translates" to the settings in the visual designer (at this point you may find it easier to work in the SQL pane) 2. If that does not function, try SELECT [DD2 LIST].Email1Address, [DD2 LIST].[FirstName] & " " & [DD2 LIST].[LastName] AS MergedName, Len(Trim("Email1Address")), Asc(Mid(Trim("Email1Address"),1,1)) FROM [DD2 LIST] which may give some further info. Peter Jamieson http://tips.pjmsn.me.uk Susan May wrote: Here Peter: SELECT [DD2 LIST].Email1Address, [FirstName] & " " & [LastName] AS MergedName FROM [DD2 LIST] WHERE ((([DD2 LIST].Email1Address)=Len(Trim("Email1Address")) Or ([DD2 LIST].Email1Address)=Asc(Mid(Trim("Email1Address"),1,1) ))); "Peter Jamieson" wrote: Susan, What am I doing wrong? I don't know - not necessarily anything. NB I was intending these test queries to be executed entirely within Access - is that what you are doing? Can you please a. look at the SQL for your query (if you haven't done this before, try right-clicking in the title bar of the query designer and selecting the SQL option) and copy/paste a copy here? b. tell us exactly what the error message says? Peter Jamieson http://tips.pjmsn.me.uk Susan May wrote: Peter when I do this WHERE mystring is not null AND trim(mystring) '' I get an error message about the string 2048 characters so it doesn't work. I do this - len(trim(Email1Address)) asc(mid(trim(Email1Address),1,1)) and no records appear. What am I doing wrong? Susan "Peter Jamieson" wrote: 1. FWIW you can probably reduce the criteria I suggested to WHERE mystring is not null AND trim(mystring) '' since the other condition should be taken care of by the trim() condition. 2. I think the next thing to do is check what's actually in that field in some of the records that should be eliminated. (I tend to work directly on the SQL, but if you are more familiar with the graphical designer I'd try to stick with that). 3. e.g. add a few columns to your query such as len(trim(Email1Address)) asc(mid(trim(Email1Address),1,1)) If the address looks blank but len(trim(Email1Address)) is 0 then the field probably contains invisible non-space characters. The asc() function should tell us what the first character is. You can use asc(mid(trim(Email1Address),2,1)) etc. to look at the other characters. Peter Jamieson http://tips.pjmsn.me.uk Susan May wrote: Peter: I tried this WHERE mystring is not null AND mystring '' AND trim(mystring) '' with mystring name as Email1Address, and it created 3 different columns with the criteria "is not null"; "; and Trim(Emai1lAddress) with criteria ", and I'm still getting blank emails. What did I do wrong? "Peter Jamieson" wrote: Also, when I set up my query, I selected the criteria for the email address field "is not null", and there are still some blank records with no emails. Different software packages can treat "null", "a string set to ''", and "a string containing white space differently, and may also treat variable-length and fixed-length data differently in this respect. As far as Access is concerned, in some cases if you enter "white space", Access leaves the field value as "null". However, if you set a text value to '' in code, it isn't regarded as null but will otherwise look no different to the user in many cases. So generally speaking, you have to test a string for both null and blank. to detect that in the SQL in Access you need something like WHERE mystring is not null AND mystring '' If the string could contain one or more spaces, you would probably need to add another condition, e.g. WHERE mystring is not null AND mystring '' AND trim(mystring) '' but if mystring could contain other types of "whitespace" such as tabs, non-breaking spaces, I think you would need to ask an Access person. You might be able to use LIKE with a pattern to do it. Peter Jamieson http://tips.pjmsn.me.uk |
| Reply |
| Thread Tools | |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Merge to email reverses the order of email address with addressed | Mailmerge | |||
| Merge to Email not using email address in data file | Mailmerge | |||
| e-mail merge errors w/ multiple email addresses in email field | Mailmerge | |||
| merge a single email to all the email address for a contact | Mailmerge | |||
| Enhance merge to email feature of word with cc: merge field. | Mailmerge | |||
Linear Mode
