Help with mail merge function

[harryfraser]

Hi Guys,

I have included a function in the database so as when a user enters an email address it stores it as a hyperlink.

The user can then click on the hyperlink and it opens a new email message to that person. This is great when sending an email to individuals when using the database, however there are occasions when we need to perform a mail merge to send the same email to multiple users. At present if i try this it attempts to send the emaill to #mailto:abc@abc.com.

Is there a way that I can create a query to remove the '#mailto:' part just for the purpose of mail merge campaigns - I want to keep the hyperlink on the main forms as it is quite a useful feature.

Thanks

Harry

 

[CBrighton]

If I assume that the email field is called Email then you can add the following field to a query:

MassEmail: Right(Email,len(Email)-8)

This will be the contents of the email field minus the first 8 chars.

 

[harryfraser]

Hi Chris,

Sorry, just realised there is a # at the end of the email. How can I get rid of this too? The rest of code is fine to remove the 'mailto' part.

Thanks

Harry

 

[CBrighton]

Hmmm... try:

MassEmail: mid(Email,9,len(Email)-9)

You may need to tweak the details as this is untested code.

This should start at character 9 of the field and capture the number of characters minus 9 (8 for the #mailto: and 1 for the # at the end)

 

[harryfraser]

Just the trick...cheers!

 

[smig]

one note to think about:
when you send email this way all users will get all email addresses.
I think it's not advised.

 

[harryfraser]

I'd disagree with that smig - it sends each email individualy.

 

[smig]

I'd disagree with that smig - it sends each email individualy.

If it does forget what I said

 

[luism]

This was helpful, however my issue is a bit different.

How would I tell my query to get rid of the #mailto:email# that appears like this

abc@abc.com#mailto:abc@abc.com#

So strip that format so it could be just a normal email?

 

[John Big Booty]

Have a look at the Replace() function.

 

[luism]

i have no idea where to begin, im sure its easy cake for an advanced SQL guy

 

[John Big Booty]

Something along the lines of;

Replace("abc@abc.com#mailto:abc@abc.com#",[URL="http://www.techonthenet.com/access/functions/string/mid.php"]Mid[/URL]("abc@abc.com#mailto:abc@abc.com#", [URL="http://www.techonthenet.com/access/functions/string/instr.php"]Instr[/URL]("abc@abc.com#mailto:abc@abc.com#", "#"), [URL="http://www.techonthenet.com/access/functions/string/len.php"]Len[/URL]("abc@abc.com#mailto:abc@abc.com#") - Instr("abc@abc.com#mailto:abc@abc.com#", "#")))))

You may need to play around with some of the expressions to get the desired result, but it will get you started.

 

[luism]

Here is what I'm doing, I created a report and I'm making a macro email the report and grabbing the contact's Email, however it is creating something like this

contactemail@whereever.com#mailto:contactemail@whereever.com#

My goal would be to write a query to remove anything in between the #'s of course along the #'s so that when the macro runs it only creates the normal email on the TO field

Thank you for the guidance!

 

[John Big Booty]

OK, the code is actually;

=Replace([RawEmail],Mid([RawEmail],InStr([RawEmail],"#"),Len([RawEmail])-InStr([RawEmail],"#")+1),"")

See the Control Source for the field Correct Email on the form.

 

[luism]

amazing, that would've taken me days to weeks to figure out. thank you SO very much, now the email TO field works and the email sends without issues.

 

[John Big Booty]

Glad I could help you