Sending e-mails using Access (1 Viewer)

[dz2k7]

Hi guys,

My VBA in access uses ready made Excel files and sends them out as an attachment to the e-mail to 250 branches according to the e-mail address list which is a table in Access like.

BranchName EMail
WXP Branch.Manager@Company.ca


Each Excel file has Branch name as a file name like WXP.xls

I have 2 problems.

1. someties i have a record in my list of branches but the file does not exist.
How to check if file exists?

2. when Access send the e-mail out I have a message from Outlook that some porgram wants to send e-mail automatically and asks me to hit Yes button.
So i have to do that 250 times.
Does somebody knows how to get rid of that?

Thanks to everybody.

 

[HiTechCoach]

1) I use the DIR()

2) I have used: http://www.contextmagic.com/express-clickyes/

 

[dz2k7]

Dir works fantastic!
Thanks a lot!

For the second one i can't see this site by our security reasons.
Could you please to make a quote from there?

Thanks again.

 

[HiTechCoach]

Sure, here is some info about ClickYes

ClickYes Software is your helping hand in solving issues caused by these Outlook warnings. There are two versions available at the moment: Express ClickYes and ClickYes Pro.

Both of them work in the background. You will hardly ever remember of ClickYes once it was installed and configured. Both of them are for allowing your email application work without any delays. But though they are very similar by name, they are completely different from each other by design.

While Express ClickYes just clicks the Yes button on behalf of you, ClickYes Pro configures Outlook security settings and prevents the Outlook security prompts from appearing at all.

 

[HiTechCoach]

I also use send directly using SMTP to avoid any email client.

Example:

Const cdoSendUsingPickup = 1 'Send message using the local SMTP service pickup directory.
Const cdoSendUsingPort = 2 'Send the message using the network (SMTP over the network).

Const cdoAnonymous = 0 'Do not authenticate
Const cdoBasic = 1 'basic (clear-text) authentication
Const cdoNTLM = 2 'NTLM

Set objMessage = CreateObject("CDO.Message")
objMessage.Subject = "Example CDO Message"
objMessage.From = """Me"" <me@my.com>"
objMessage.To = "test@paulsadowski.com"
objMessage.TextBody = "This is some sample message text.." & vbCRLF & "It was sent using SMTP authentication."

'==This section provides the configuration information for the remote SMTP server.

objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

'Name or IP of Remote SMTP Server
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "mail.your.com"

'Type of authentication, NONE, Basic (Base64 encoded), NTLM
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = cdoBasic

'Your UserID on the SMTP server
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusername") = "youruserid"

'Your password on the SMTP server
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "yourpassword"

'Server port (typically 25)
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25

'Use SSL for the connection (False or True)
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = False

'Connection Timeout in seconds (the maximum time CDO will try to establish a connection to the SMTP server)
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60

objMessage.Configuration.Fields.Update

'==End remote SMTP server configuration section==

objMessage.Send

 

[HiTechCoach]

I have not used this, but it is popular:

Outlook Redemption
http://www.dimastr.com/redemption/

Outlook Redemption works around limitations imposed by the Outlook Security Patch and Service Pack 2 of MS Office 98/2000 and Office 2002/2003/2007 (which include Security Patch) plus provides a number of objects and functions to work with properties and functionality not exposed through the Outlook object model.

As of version 4.0, Redemption library also includes the RDO (Redemption Data Objects) family of objects that can function as a CDO 1.21 replacement.

With Outlook Redemption you can

* Make your code run unaffected by the Security Patch.
* Access properties not exposed by the Outlook Object Model (internet message headers, sender e-mail address and hundreds more properties)
* Display Address Book.
* Directly access RTF body of any Outlook item
* Import MSG, EML (RFC822) and TNEF files
* Export messages to the MSG, EML, TXT, HTML, TNEF and vCard formats.
* Access and manipulate Outlook accounts (Outlook 2002 and above, RDO library)
* Create, access and manipulate MAPI profiles and accounts (Profman library)
* Access MAPI stores (not exposed in the Outlook Object Model at all)
* Directly access message attachments as strings or as arrays without saving them as files first
* And much, much more!

Redemption supports Outlook 98, 2000, 2002, 2003 and 2007 (Outlook 97 is not supported by the Safe*Item objects). The RDO family of objects can also be used with the Exchange Server versions of MAPI.

 

[dz2k7]

I tried ClickYesPro.

It works fine!

Thanks a lot for your support.

You are really COACH

 

[HiTechCoach]

You're welcome!

Glad to assist.

 

[Mike375]

HiTechCoach,

I just tried that code of your in a function and got it going.

Is this designed to work when the person does not have Outlook. If so, I can stop looking.

I do need to make a couple of other adjustmenst such as getting the email to send to and the message from Access texboxes AND also do something so the email does not go into Windows mail Junk Mail. I justested it emailing myself and it laned in jumk mail

But great stuff you have there.

I had no idea where to start. Outlook is easy as that is much the same as Access to Word.

Can attachments be done with it?

 

[Mike375]

Doing the attachments using SMTP will be a challenge

 

[Mike375]

I can't believe it. I have been fiddling around for 2 hours wading through pages of code on the business of attachments, and some of it is so long and with warnings of how hard it all is with SMTP. I was about to put it to one side for the day, but had one more go and presto

objmessage.AddAttachment ("C:\StoreLetters\" & Format([Forms]![PrintAndClose]![LNameNoDoc]) + ".doc")

Same as for Outlook except no dot between Add and Attachment.

That code of yours for the SMTP and with the attachment seems to work much better than the Outlook and with out that bullshit of the Send/Security warning.

 

[HiTechCoach]

I can't believe it. I have been fiddling around for 2 hours wading through pages of code on the business of attachments, and some of it is so long and with warnings of how hard it all is with SMTP. I was about to put it to one side for the day, but had one more go and presto

objmessage.AddAttachment ("C:\StoreLetters\" & Format([Forms]![PrintAndClose]![LNameNoDoc]) + ".doc")

Same as for Outlook except no dot between Add and Attachment.

That code of yours for the SMTP and with the attachment seems to work much better than the Outlook and with out that bullshit of the Send/Security warning.

Mike375,
Glad you liked it!

 

[Mike375]

Mike375,
Glad you liked it!

I more than liked it

I stumbled across your post just as I was getting ready to have a go at the SMTP and was expecting one of those sessions where the answer comes just as the sun is rising

In fact I almost did not try the forum because I though whatever was here would be just for Outlook.