Generating e-mails through Access-VBA 4

[HenriM]

I have included this line in an application which I am publishing on a website:-

[DoCmd.SendObject acSendReport, "Mail Permissions", acFormatXLS, "instantprofile@comptonit.com", , , "Free Client"]

The objective is to enable users to send me a completed report by e-mail when they want to utilise a free facility.

Trying this out on my own system,however, the above command directs me to Microsoft Mail and asks me to open an Account. I assume that this would also happen to any prospective user and it is of course necessary for it to send the e-mail through the user's own ISP.

If it is possible to do this direct from Access this is greatly preferred and any help would be welcome.

If not, then it would be just as helpful to have confirmation so that I can approach the requirement from a different angle.

I have already placed this query on another forum but have so far had no response.

 

[Junior1544]

SendObject will automaticly use outlook or outlook express... by default (I know it uses Outlook, not sure about oe...)

if you use a different program then sendobject wont work...

here is a link to a microsoft page that gives information about how to make lotus notes cc mail work with that object...

http://support.microsoft.com/default.aspx?scid=KB;EN-US;q112133&

other then this information i don't know... sorry...

--James junior1544@jmjpc.net
Life is change. To deny change is to deny life.

 

[nat1967]

Hi,

Docmd.sendobject does require a mapi client if I am not mistaken. If your pc or the users pc does not have a mapi client, you would receive what you are describing. Also, you didnt set a variable for editing the message or not. I assume you do not wan the users to edit their email just send it.

try this:

DoCmd.SendObject acSendReport, "Mail Permissions", acFormatXLS, "instantprofile@comptonit.com", , , "Free Client",,False

Adding False at the end sends the email without allowing for user edit.

HTH

Nathan
Senior Test Manager

 

[RichD]

Have you checked the FAQ area of this forum. There is a good section on emails, FAQ 4e might help as it uses the Shell command which should be able to open any emailer.

HTH

Rich

 

[mp9]

Better than the Microsoft code mentioned earlier, try

http://www22.brinkster.com/accessory/modules/005.shtml

instead. This does everything the MS code does but also lets you specify the "From" address, set message importance, add voting buttons, etc. It is dependant on you using Outlook though.

 

[HenriM]

I have opted for the Shell option (FAQ 4e) - thanks again to RichD - as it gives flexibility for automated handling of other parts of the procedure; however, I am having a problem with SW_SHOWNORMAL in the ShellExecute Function.

My code produces the following string:-
mailto:henri@comptonit.com?CC = BCC = Subject = Free Client Body = XYZ Consultants,R Roberts,299 High Road Whitefield,MANCHESTER,M75 8AZ,0161 999 9999,0161 666 6666,rrob@myaddress.com Attach =

Running the procedure I get an Error Message "Compile Error – Variable not defined" with SW-SHOWNORMAL Highlighted.

To make sure, I recreated the structure exactly as per the code in the faq and the result is the same except that stext now omits the null CC,BCC and Attach entries so these would seem to be superfluous in my code.

I still get the same error message.

I then tried “SW_SHOWNORMAL = 1” in the Call – same message.

I then tried deleting nShowCmd from the module and SW_SHOWNORMAL from the Call. But that gave me another Error – “Expected variable or procedure not Module” so obviously these are an integral part of the ShellExecute propcedure.

Finally, I tried [Dim SW_SHOWNORMAL as Long
SW_SHOWNORMAL = 1]

This also gave the second Error Message.

“If at first, second, third, fourth and fifth you don’t succeed – go back to Tek-Tips!"

 

[9AndyC]

You have reached the same point as I have on this one. Using the code provided by GAQ 4e links me to mail just fine no errors but the set up doesn't seem to cope beyond CC, what I really need is to get to the correct coding to add an attachment. I suspect SW_SHOWNORMAL should be 1 to open and show mail. I just substituted the number 1 in the call.
I am working on it and am going to use your sample string as a start point. Keep you posted, keep trying and let me know if you succeed.

 

[HenriM]

9AndyC

I tried various things following on your reply but nothing worked so I won’t bore you with them.

I looked up the FAQ again, and this gave the option to comment to the author DougP. So I have copied both my last question and your reply to him with the comment that we would both appreciate his help.

I am sure he will respond

 

[FancyPrairie]

mp9, I wrote some code pretty much similiar to the thread you suggested

http://www22.brinkster.com/accessory/modules/005.shtml

However, my didn't work with mutiple names, so I copied and pasted the function referred to in your link and it would not except multiple names. What am I doing wrong?

Here's how I call it:

fctnOutlook , "LastName1, FirstName1; LastName2, FirstName2", , , "This is a test", "this is a message"

Gives me the error: "Outlook does not recognize one or more names".

But, if I call fctnOutlook with just one of the names (either one) it works.

Any ideas?

 

[9AndyC]

Some success. My problem lies in the fact that I need to mail using Lotus Notes. I used the following string and switched to outlook and bingo I got mail. When I switched back to Notes it didn't form the same message. (Nothing after CC) any thoughts ?

mailto:acreamer@csc.com?CC=acreamer@csc.com&BCC=acreamer@csc.com&Subject=Test Email&Body=Test Content&Attach="e:\ben.snp"

 

[danvlas]

FancyPrairie: replace comma with semicolon as separator between names. I really don't know if it will work, but I've seen systems that do NOT work if the mail address separator is a comma. Using a semicolon worked every time, regardless of Regional settings....

HTH

Daniel Vlas
Systems Consultant
danvlas@yahoo.com

 

[nking]

Best idea so far is to use SMTP to send the email. This means you may have to buy an ActiveX control to send the email for you. Also the users will have to have access to the SMTP server (hope it is on the Internet and they can see it?) to send mail.

Or you can research the Winsock controls and write your own.

Not everyone will have the same Email client as you, and some may not even have a MAPI client that will work with the SendObject command. You will have to find an SMTP ActiveX control that can embed the file as part of the email.

Go here:

http://www.activex.com/

 

[FancyPrairie]

danvlas, thanks for responding. I do have a semicolon in the string. Note that "Smith, John" and "Smith, Mary" work but "Smith, John;Smith, Mary" does not work. Do you suppose it's the comma separating the last and first name that may be the problem?

 

[danvlas]

FancyPrairie: I'm not sure, as I said I found this bug for lists of email addresses, not first + last names, but it's worth a try. Even though it may be shooting in the dark...


Daniel Vlas
Systems Consultant
danvlas@yahoo.com

 

[mp9]

FancyPrairie, try encapsulating thje names in single quotes.

i.e.

fctnOutlook , "'LastName1, FirstName1';'LastName2, FirstName2'", , , "This is a test", "this is a message"

Tech problems of my own prevent me from testing this at the moment, but I think this'll work.

http://www22.brinkster.com/accessory/

 

[FancyPrairie]

mp9, I thought you were on to something, but it didn't work. If I examine objoutlookrecip.Name everything looks ok. However, the following section doesn't produce what I would expect.

For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
Next

I would assume that the for...next loop would execute twice. The first time thru the loop objOutLookRecip.Name would be "LastName1, FirstName1". And the 2nd time it would equal "LastName2, FirstName2". However, it only executes the loop once and produces this result: "LastName1, FirstName1;LastName2, FirstName2".

 

[mp9]

If you comment out

For Each objOutlookRecip In .Recipients
    objOutlookRecip.Resolve
Next

does this function work? The

Resolve

command just gets Outlook to check the recipients against your Address Book.

http://www22.brinkster.com/accessory/

 

[FancyPrairie]

mp9, thanks for sticking with me. Tryed what you suggested, it made sense, but didn't work.