Problem sending emails where there are 2 email addresses

[Baldeagle]

I am not experienced in VB but have received help in the past. One part of the code for sending emails from Access through Outlook that I received in the past is
strEmail = """" & .Column(1, varItem) & ""
where the information that is picked up from Column 1 is either simply '***@gmail.com' or '***@Gmail.com; ***@talktalk.net'. This has worked in the past but today where there is only one email address in this field everything works OK but it throws up a problem then there are 2 - the message reads 'Run-time error 2205. Unknown message recipient. the message was not sent'. If I copy the 2 email addresses from the field and paste them into Outlook there is no problem sending an email to both. Has something happened in some download from MS that is causing this problem? Does the wording of the strEmail cause the problem? If so, can it be modified simply to handle either 1 or 2 email addresses?

[ranman256]

Just send 2 separate emails,by looping thru the list.
access doesn't care how many it sends.

[Baldeagle]

Unfortunately I didn't provide sufficient information in my original post to help you to fully understand the problem!

We have a large number of organisations that we write to and some of these want us to send our emails to 2 particular addressees. So the programme scrolls through each of the organisations and then picks up the email addresses from the Email field. It is that field that can contain either 1 or 2 email addresses. So the strEmail line in the code picks up the email addresses and the rest of the code sends the emails.

I trust this helps you to understand the original question.

[davegri]

Put a 'Debug.Print strEmail' in the code just after setting the value. It will show you what is being passed in the TO: field.
After we see the format when trying to send more than one, we can see how to adjust code for 2 addresses.

[Baldeagle]

I have done as you suggest, and my 2 lines of code now are as follows -
strEmail = """" & .Column(1, varItem) & ""
Debug.Print strEmail
Is it supposed to stop at that point and show the value of strEmail? It just sails on to the end of the code! Where should I look for the value of strEmail?

[pbaldy]

Here's a picture of where to see it:

BaldyWeb-Immediate window

[Baldeagle]

That should be very helpful, pbaldy. But when I look at my VB code I can't see anything like your 'Immediate' section. As you will appreciate I am not very familiar with Visual basic so is there something that I still need to do to replicate what you have demonstrated in your link? Sorry to be a nuisance!

[pbaldy]

In the VBA editor, click on View/Immediate Window, or Ctrl-G.

[Baldeagle]

Brilliant!! What it shows is "***@gmail.com; ***@talktalk.net. There is " in front of the first email address and nothing at the end. This seems to be the same as when there is only one email address in the field. Does this throw any light on why it won't send the 2 emails? Because a single email address is sent OK.

[davegri]

strEmail = .Column(1, varItem)

Try above. I don't see any need for the apostrophes. It's already a valid string.

[Baldeagle]

Thanks for that advice. I have used your amended code and it works!! I appreciate your assistance. All I need now is help on the query that I put into the Outlook forum at the same time because I am getting this message - 'A programme is trying to send an email message on your behalf....' and the operator in Access has to click on 'Allow' for every individual email. If you can help on that in the MS Office (Outlook) forum that would complete my problems for the present.

In the meantime I will mark this thread as Solved with thanks to everyone who has contributed.

[davegri]

Others can advise you at the Outlook forum, where that expertise lies.
Glad we could help here.