Sending email directly from Access with attachment?

[anb001]

I'm looking for a way to send an email with a single attachment from Access (I'm using Access 2007, but it should be used in Access 2003).

On the attached screenshot, I have this controls: cmdTO, txtTO, cmdCC, txtCC, txtSubject, txtText, cmdSend and cmdCancel.

If the user clicks either the cmdTO or cmdCC button, a small form opens, where each recepients email address can be entered, one per line. When clicking ok in that form, the addresses should be saved in a table and the email addresses should be transferred to txtTO, seperated with a "; ". The user should fill out txtSubject and txtText boxes.

When clicking the cmdSend button, then the email should be send directly to the stated email addresses, with a a report (rptReport) attached. The report should be printed to a pdf file (I have adobe acrobat installed on the work pc supposed to run this) before being attached and send. If possible, I would like the Report which is being printed and send, to be called a specific name + 'todays' date.

I would like to code a fixed senders email address, as the email should be send directly from Access.

I have looked at, searched and read a lot of different posts at various places on the web, however I'm quite confused how to get this started and done. I hope someone can assist me in getting this started, and guided the right way.

I should mention that we use Novell Groupwise as mail system on board, however I assume it doesn't matter, since the emails should be send directly from Access, making it unimportant which mail system is being used.

/Anders

 

[Xproterg]

The following code can be used to send email.

Dim OutApp As Object
    Dim OutMail As Object
    '-- Standard Email Variables
    Dim Variable_To As String
    Dim Variable_Subject As String
    Dim Variable_Body As String
    
    
    '-- The e-mail address to where the ticket will be sent.
    Variable_To = txtto.value
    
    '-- The Subject of the email
    Variable_Subject = txtsubject.value
 
    '-- The Body of the email
    Variable_Body = txtBody.value

    Set OutApp = CreateObject("Outlook.Application")
    OutApp.Session.Logon
    Set OutMail = OutApp.CreateItem(0)
    On Error Resume Next
    With OutMail
        .To = Variable_To
        .CC = ""
        .BCC = ""
        .Subject = Variable_Subject
        .Body = Variable_Body
        .Display   'or use .Send
        .ReadReceiptRequested = True
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
    
    Dim Msg, Style, Title
        Msg = "Email is ready for review... " & Chr(13) & Chr(10) & "Press OK to continue."
        Style = vbOKOnly + vbInformation
        Title = "Open Issues List"
        Response = MsgBox(Msg, Style, Title, Help, Ctxt)

 

[anb001]

Xproterg, thanks, but as far as I can see from your code, it uses Outlook as application. As we don't have Outlook, I need the email to be send directly from Access, as if no email client had been installed on the computer.

/Anders

 

[John.Woody]

Search the forum for CDO email. This should do what you need.

 

[JANR]

Send email using CDO: http://www.paulsadowski.com/WSH/cdo.htm

JR

 

[anb001]

I found some info on the cdo, and someone in the forum have made a small test database, where you pretty much just have to change smtp, port, etc details. When I use it from my laptop, setting in up with gmail smtp, port and other necessary settings, it works fine.

However, when I use it from my work computer, then I get an error stating that I can't get through to the server (The transport failed to connect to the server. 80040213), and it can't send any mails. It is still with the gmail smtp settings. Does anyone know how come I can't get through on the work pc, but it is possible on my laptop??

I then tried with the smtp ip, username, passwork, port etc, which is used by my work email, but I still can't get through. As it is my work pc, then I do assume that some kind of firewall is in use. Can that be the problem? And it there any way to get aound that??

/Anders

 

[anb001]

It seems we are using MAPI and not SMTP when sending emails. Not that I have a big clue about the difference, I have read that there indeed is a difference. Could that be the reason why I can't get the CDO code to work??

I have attached a few screenshots (taken from Outlook), and it shows the Groupwise server and port. Apparently this is not the SMTP.

Any ideas if I can send emails from Access using MAPI? Or doesn't that make sence??

/Anders

 

[smig]

I made a full email application. I'll post it in few days into the Code Repository section
I'm using vbSendMail.dll to send the emails

 

[anb001]

Smig,
That sounds good. Looking forward to that.

I have, however, found a solution to my problem sending email from behind a firewall, where I can't open ports.

I found this link in a post elsewhere in this forum: http://www.granite.ab.ca/access/email.htm andf a bit down the page is a link to code which can be used with novell Groupwise. Works like a charm

No need to enter information about smtp, port, username, password etc, and it still sends directly from Access. It also put the emails send, into the Groupwise 'send' folder.

I'll consider this post solved now. I'll create a seperate post regarding the small additional questions.

/Anders

 

[smig]

I just sent it to the Sample Databases
it will be there in a day or two