Send e-mail from access databases 65

[tpowers]

Now I am a new guy on the block, so bear with me. I have a form that users are entering client information into all day, and I have been asked to set the database up so that when the are done entering the record that it will automaticlly send the client a confirmation e-mail that the record as been recieved and entered into our database.

Again I am new at this so if so one as a clue of how to do this that would be great.

 

[tpowers]

Nathan, this is how it works. I have to forms, the first form is a read only form that will allow users to view a record, the second form will allow them to add follow up notes to the record. This is what I have done, I created to tables. The frist table is the primary table that holds the record, the second table is where the follow up information is stored. I linked the two tables together and then created two forms, the first form being the read only form connected to the primary table and the second form being the update form that is connected to the secondary table. Now what happens is the user will open the first form and pull up a record, if that record needs to have notes added to it then the user can click a button that will open the second form with all the same data that was on the first form plus the option to add more information. After they submit this form they will need to be able to go back into the record and view all the old information on the first form and all the new information on the second form.

It would be great if you could help me with this.

 

[nat1967]

Hi SBendBuckeye,

Thank you for your compliments!!

Here is how to add attachments:

'***begin attachment code***

'***creates and sends email
With objEmail
.To = strEmail
If Not IsNull(txtCCemail) Then
.CC = strCCemail
End If
.Subject = "Your information has been received"
.Body = strBody
.Attachments.Add ("c:\lott.txt&quot 'add attachment
.Send
End With

'****end attachment code****

Now, the saving:

I havent tried it but I found this. I hope it helps you.

Set myOlApp = CreateObject("Outlook.Application&quot
Set myItem = myOlApp.ActiveInspector.CurrentItem
Set myAttachments = myItem.Attachments
myAttachments.Item(1).SaveAsFile "C:\My Documents\" & _
myAttachments.Item(1).DisplayName

I dont have a clue about opening outside of Outlook. sorry.

Have a great day!! Please give helpful posts the Stars they deserve. It makes the post more visible for others

Nathan
Senior Test Lead

 

[nat1967]

Hi tpowers,

Sounds like you need to use subforms instead of two separate forms. The wizard does a pretty good job of setting this up for you.

In your main form (design mode), select "subforms" from the toolbar. Place the subform where you want it and the wizard will start up.

HTH Please give helpful posts the Stars they deserve. It makes the post more visible for others

Nathan
Senior Test Lead

 

[isorders]

I have used a similar example from Microsoft's site and get the browse window to open, but when I select a file name I get this error.
Run-time error '3163'
The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.

I tried a few character file names to longer ones with no luck. The text field is set to 255 characters.
Any ideas.

 

[tpowers]

Hi nathan, I liked the idea of the subform, but I don't want the subform to show untill the click on a button. Show what I tried was, I made the subforms visible properties false and then created the button that on click had this code

subform.visible = true

but instead of the subform appearing, the whole form appeared on the screen. Do yo know why that is? Did I miss something in the code.

Thank you for all your help.

 

[nat1967]

Hi tpowers,

You need to reference the Parent and the child in your command button.

Example:

Me.cust1_subform.Visible = True

This example works for me. "ME" references the parent form. "cust1_subform" references the "child".

The way your code is currently written makes Access think "subform" is the parent.

Try this and let me know.

HTH

Nathan
Senior Test Lead

 

[POKY]

hi tpower and all,
i think this will help you.
from:

http://www.fmsinc.com/go.asp?298

===================================================
Tip #10: Late Bind Subforms on Tab Controls
Provided by: Steve Clark, Senior Project Manager

Now that Tab Controls are common in Access, it's common to have multiple subforms, to the point that a main form will take a very long time to open.

Using the following code, you can leave the subform unbound, until the user clicks on the tab. This on-demand technique will allow the main form to load without trying to populate all of the subforms.

Private Sub tabStuff_Change()

Select Case tabPermit.Pages.item(tabStuff.Value).Name
Case "pg1"
If Len(sfrm1.SourceObject) = 0 Then
sfrm1.SourceObject = "frmStuff_1"
sfrm1.LinkChildFields = "StuffID"
End If
Case "pg2"
If Len(sfrm2.SourceObject) = 0 Then
sfrm2.SourceObject = "frmStuff_2"
sfrm2.LinkChildFields = "StuffID"
End If

'Repeat for each tab

End Select

End Sub

 

[tpowers]

Hello nathan1967,

I am back but this time I have a very samll question.

I am trying to update the above e-mailing code that you sent. What I am trying to do is make it so that when you submit the e-mail it will display the on the screen so that you can see what you are about to send, and then the user can just click send on the e-mail when they are ready to send it. Do you think you can help me with this.

 

[nat1967]

Hi tpowers,

You know.... come to think of it... to my knowledge, Outlook doesnt have a "visible" object that we can call. hmmm... let me do some checking and try a few things. i will post back or maybe someone who does know how will post before I get can.

anyway... stand by... i will post back.

Have A Great Day!!!,

Nathan
Senior Test Lead

 

[tpowers]

FYI, I was told to use something called mapi but the person that told never went into detail about it maybe you or someone else knows what I mapi can do and if it will help me.

 

[wkendrvr]

Personally my solution was to shell to Blat emailer. It allows attachments and doesn't use Outlook at all. It does require that you have a SMTP server available tho.

 

[tpowers]

I was given this but, I don't know what to use or where to place it in the code above.

http://support.microsoft.com/default.aspx?scid=KB;en-us;153311&

Maybe this can help some one to help me.

or is this something completely different.

 

[nat1967]

tpowers,

I expect you might end up using something like MAPI to accomplish what you want. Outlook does not have an Object for a visible property so... you will have to use another way to accomplish the changes you want. well, at least from what I have been able to find.

there is a database example and some other stuff at Dev Ashish's website. Check it out. Maybe it will help.

http://www.mvps.org/access/modules/mdl0019.htm

Have A Great Day!!!,

Nathan
Senior Test Lead

 

[danvlas]

tpowers and nathan1967:

Instead of
.Save
type
.Display

and the message will pop up o the screen.

Regards,


Daniel Vlas
Systems Consultant
danvlas@yahoo.com

 

[danvlas]

Ooooops... sorry

Instead of
[COLOR=red.Send[/color]
type
.Display



Daniel Vlas
Systems Consultant
danvlas@yahoo.com

 

[nat1967]

Hi Daniel,

DOH!!!!

What can I say... but thanks!

i have to say i was chasing a "visible" property. other office products use that and i knew outlook didnt.

Thanks again for your input and help.

hmm...note to self...display not visible in outlook.... Have A Great Day!!!,

Nathan
Senior Test Lead

 

[tpowers]

Hi Daniel and Nathan,


I just want to say that THANK YOU TO BOTH OF YOU,

Man that one was right under my nose!

This star is for both of you.

 

[tpowers]

Just when you thought that I was gone.


I was wondering why on some pc's outlook opens with no problems and on others I still get the project not found or something like that. I tried to reference it by going Tools>References and selecting the reference for ms outlook but that does not work.

do you have any clue why and what I could do to fix this problem.


Thank you in advanced.

 

[sha76]

Hi,

Do you know where your code's failing & the exact error message?

Sharon

 

[tpowers]

Ok,

The code is failing at Dim objOutLook as OutLook.Application

and I am getting an erro message that states "Compile Error: Can't Find Projectr or Library.

Help me please