sending an email composed with attachments in access 2007

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

huma

Thread Starter
Joined
Jul 25, 2007
Messages
82
Need a mail link in a DB that can attach my company's profile, latest quotation and letter of introduction to a mail from within the db as a new client is created. these attachments can be .docx / .pdf / .jpeg etc. And consequently we have a status of what has been sent to a particular client as an attachment.This DB is for a product and contains all relevant entities like meetings - targets - opportunities - campaigns - clients.
 

OBP

Joined
Mar 8, 2005
Messages
19,895
This VBA code sends a single attachment to a single recipient.

Private Sub Command16_Click()
Dim objOutlook As New Outlook.Application ' outlook object
Dim objMessage As MailItem, strAttach As String ' outlook mail message
DoCmd
strAttach = "c:\Access\West Coast flier.pdf"
Set objMessage = objOutlook.CreateItem(olMailItem)
With objMessage
.To = "Email address here"
.subject = "Test Send"
.Body = "This is a Test"
.Attachments.Add strAttach
.Send
End With
Set objOutlook = Nothing
Set objMessage = Nothing

End Sub


This code sends a single attachment to multiple recipients.

Private Sub Command6_Click()
Dim subject As String, Body As String, EmailAddress As String, objOutlook As New Outlook.Application
Dim count As Integer, rs As Object, counter As Integer, recount As Integer, objMessage As MailItem, strAttach As String
subject = Me.Text0
strAttach = Me.Attachment
Set rs = CurrentDb.OpenRecordset("Contestants emails")
rs.MoveLast
recount = rs.RecordCount
rs.MoveFirst
For count = 1 To recount
EmailAddress = rs![email Address]
Body = "Dear " & rs.Fullname & " " & [Text1] & Chr$(13) & [Text2] & Chr$(13) & [Text3] & Chr$(13) & [Text4]
Set objMessage = objOutlook.CreateItem(olMailItem)
With objMessage
.To = EmailAddress
.subject = subject
.Body = Body
.Attachments.Add strAttach
.Send
End With
Set objOutlook = Nothing
Set objMessage = Nothing
With rs
.Edit
!email = 0
.Update
.Bookmark = .LastModified
End With
If rs.EOF Then Exit For
rs.MoveNext
Next count
rs.Close
Set rs = Nothing

End Sub
 

huma

Thread Starter
Joined
Jul 25, 2007
Messages
82
Thanks OBP for your generous response - let me try this and i get back.
 

huma

Thread Starter
Joined
Jul 25, 2007
Messages
82
hey OBP
i used a single attachment to a single recipient code from you. & used the code as below:

Private Sub Command38_Click()

Dim objOutlook As New Outlook.Application ' outlook object
Dim objMessage As MailItem, strAttach As String ' outlook mail message
DoCmd
strAttach = "C:\My Downloads\Doc 4.PDF"
Set objMessage = objOutlook.CreateItem(olMailItem)
With objMessage
.To = "[email protected]"
.subject = "Test Send"
.Body = "This is a Test"
.Attachments.Add strAttach
.Send
End With
Set objOutlook = Nothing
Set objMessage = Nothing

End Sub
It gives compile error : user-defined type not defined
and highlights objOutlook As New Outlook.Application

now what to do!
 

OBP

Joined
Mar 8, 2005
Messages
19,895
huma, you need to set a VBA Library Reference to Outlook.
Open the database and your form. Press Alt + F11 to open the VBA Editor. On the VBA Editor's Main Menu click Tools>References, if any of the ticked reference links show "Missing" in their title make a note of the name and then delete them. Now use the side slider of the list to find the equivelent reference for your version of Access.
You will be looking for soemthing like
Microsoft Outlook 11.0 Object Library
 

huma

Thread Starter
Joined
Jul 25, 2007
Messages
82
Hi OBP
I found Microsoft Outlook 12.0 Object library - ticked it and close this window. Clicked the button and it gives Compile error : Invalid use of property and high lights DoCmd

now!
 

OBP

Joined
Mar 8, 2005
Messages
19,895
Sorry I don't know how that got in there, ca you just delete it.
 

huma

Thread Starter
Joined
Jul 25, 2007
Messages
82
thanks OBP its compiling alright now. there is some add in issue in my outlook, m looking into that. Meanwhile, I use thunderbird as default email client as outlook troubles me usually. is there any way i can connect it with that?
 

OBP

Joined
Mar 8, 2005
Messages
19,895
huma, not as it is, there is VBA code for use with any Email program, but I am not sure where to find it at the moment.
 

huma

Thread Starter
Joined
Jul 25, 2007
Messages
82
ok i sorted out my outlook's add on error issue and went back to the form. clicked the button - no error but nothing happens!!! :(
this is what i've written thre:
Private Sub Command38_Click()

Dim objOutlook As New Outlook.Application ' outlook object
Dim objMessage As MailItem, strAttach As String ' outlook mail message
strAttach = "C:\My Downloads\Doc 4.PDF"
Set objMessage = objOutlook.CreateItem(olMailItem)
With objMessage
.To = "[email protected]"
.subject = "Test Send"
.Body = "This is a Test"
.Attachments.Add strAttach
.Send
End With
Set objOutlook = Nothing
Set objMessage = Nothing

End Sub


I'm also in process of trying the code for other mail clients.
 

OBP

Joined
Mar 8, 2005
Messages
19,895
This just worked for me.
Dim objOutlook As New Outlook.Application ' outlook object
Dim objMessage As MailItem, strAttach As String ' outlook mail message

strAttach = "c:\Access\Marathons\West Coast flier.pdf"
Set objMessage = objOutlook.CreateItem(olMailItem)
With objMessage
.To = "My email Address" ' not the real thing
.subject = "Test Send"
.Body = "This is a Test"
.Attachments.Add strAttach
.Send
End With
Set objOutlook = Nothing
Set objMessage = Nothing

Didn't you even get the Outlook Security question?
 

huma

Thread Starter
Joined
Jul 25, 2007
Messages
82
:) i replaced my code with yours - and it atleast showed me the msg from outlook 'Outlook isn't your default ...would u like to make it ur default mail ' i clicked no and nothing happen. If i'm not wrong a composed mail should have been opened then. but it didn't. Now what to do??
Also the email has to b taken from a field containing the email of the client, 'email address' from table 'Company detail'.
 

OBP

Joined
Mar 8, 2005
Messages
19,895
My outlook is not my default mail program either, but it still sent it, 3 times now.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Members online

Top