Email from access - still struggling.

[Appollo14]

Hello all,
Sorry to post on the same subject again but i dont think my last post explained things well enough to convey my problem.

I have written a small project which exports data into a blank database for the purpose of importing into an MSSQL database located on another site. So far i have given the user the ability to have an automated copy to floppy disk option and a partially automated email option. Using the SendObject command i have managed to start the email prog, insert an email address (this only works if it's a valid entry in the users address book), insert a subject title and some body text instructing the user on their next move.

The code i'm using is as follows.

'Email Transfer
Msg = "Do you want to Email the jobs? "
Style = vbYesNo + vbWarning + vbDefaultButton1 + vbSystemModal
Title = "Transfer Via Email"
Help = "DEMO.HLP"
Ctxt = 1000
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
If Response = vbYes Then
DoCmd.SendObject acSendNoObject, , , "Insert Email Address Here", , , "Customer Job Transfer", "Insert the file C:\QEsystem\Exportdata\transfer.mdb to this email & add the required email address.", True

I would really like to tidy this up and have it a lot more professional looking and user friendly, the only problem is that i'm not a programmer and i've never tried this before. However, on the plus side i'm really enjoying this and i really want to learn more.


Regards,
Noel.

 

[SgtJarrow]

Just what are you asking???? What you want to do is not that difficult....but you need to explain yourself very will.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[Appollo14]

Doh....Sorry mstrmarge, i did it again

the bit i'm stuck on is automatically attacthing the external file to the email. I can see that the sendobject handles emailing reports, tables etc but how can i get the email program (outlook express in this case) to start up with the file (c:\QEsystem\ExportData\transfer.mdb) already attatched to save my end user going and finding the file themselves.
I hope that clears things up and that it's as easy as you say

Regards,
Noel.

 

[SgtJarrow]

okay....kind of where I thought you was going with this...

First, let me begin by saying that DoCmd.SendObject works....most of the time. There are times it will fail you and because of this I have pretty much moved away from using it. Second, if you want to auto attach a file, DoCmd.SendObject just isn;t the way to go....you need to work with automation. While daunting at first, once you understand it is is not that hard.

Automation is basically the concept of using one Office product to control another...in this instance using Access to open up, create, and send a Outlook mail message.

You have to ensure you set the reference to Microsoft Outlook 9.0 (or 10.0) Objects in your VBA references. here's the code snippet:

' ************** Start Code ***************

Public Sub SendReport()

Dim otk As outlook.Application
Dim eml As outlook.MailItem

Set otk = CreateObject("Outlook.Application&quot
Set eml = otk.CreateItem(olMailItem)

With eml
.To = "rljohnso@stewart.com"
.Subject = "Test Message"
.Attachments.Add "C:\test.doc"
.Send
End With

Set eml = Nothing
otk.Quit

End Sub


' ************** End Code *****************

That's all...once you set the references and all, you will find other fields, such as .CC .BCC and others you may need. Just as your first post, you can use variables, prompt for input, and so on....Ask further questions and I will be happy to help you flesh this out...

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[Appollo14]

Thanks Robert,

I'm goin offline to try some of this out but i will probably be taking you up on your kind offer of further assistance.

Kind regards,
Noel.

 

[Appollo14]

Told you i'd be back he he..

I've come accross my first stumbling block. I cant add the reference to Microsoft Outlook 9.0 (or 10.0) to my system because i have not got Outlook installed. I was hoping to use Outlook Express to complete this task as i cant gaurantee that the end user will have ms office installed on their PC but most PC's are shipped with Outlook Express.

Can it still be done?

Regards,
Noel.

 

[SgtJarrow]

I will do some looking......hold tight.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[Appollo14]

Hi Robert,

Been doing some digging myself and came accross this.

You can use ShellExecute to send email via the default email client:

Option Explicit
Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
Const SW_SHOWNORMAL = 1



' my sub routine
Dim strTo As String
strTo = CompanyId.Column(2)
ShellExecute Me.hwnd, vbNullString, "mailto:" & strTo, vbNullString, "C:", SW_SHOWNORMAL


This works in generating an email in outlook but again i'm stuck with the problem of not having the attatchment added and to be honest the above code is beyond me and no explanation came with it.

what do you think?

Regards,
Noel.

 

[Appollo14]

Just worked out that i can get the same results by just typing mailto:me@mydomain.com into run.

I think i'm barking up the wrong tree with this bit of code.

Regards,
Noel.

 

[Lee24]

are you still looking for advice? i have got my database sending out attachments automtically

lc

 

[Appollo14]

Hi Lee,
As a matter of fact i could still do with some advice on this if you can help. I've got it working, after fashion, but it's just so messy and leaves the user with too much to do.

Regards,
Noel.

 

[Jackvmae]

Lee,
I've been following this thread. I too would like to see how you do it.

Thanks,
Jack