# sending an email composed with attachments in access 2007



## huma (Jul 25, 2007)

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 (Mar 8, 2005)

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 (Jul 25, 2007)

Thanks OBP for your generous response - let me try this and i get back.


----------



## huma (Jul 25, 2007)

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 (Mar 8, 2005)

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 (Jul 25, 2007)

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 (Mar 8, 2005)

Sorry I don't know how that got in there, ca you just delete it.


----------



## huma (Jul 25, 2007)

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 (Mar 8, 2005)

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.


----------



## OBP (Mar 8, 2005)

huma, you could try this code
http://www.tek-tips.com/faqs.cfm?fid=537


----------



## huma (Jul 25, 2007)

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 (Mar 8, 2005)

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 (Jul 25, 2007)

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 (Mar 8, 2005)

This is what I just used again.


----------



## OBP (Mar 8, 2005)

My outlook is not my default mail program either, but it still sent it, 3 times now.


----------



## huma (Jul 25, 2007)

I just open outlook mailbox & noticed a number of mails in the outbox and they were the test msgs the button was sending. 

Now the abt the zip file:
as i click *send email* this appears:
runtime error2059 Microdoft Office access cannot find 'objrptOrdersByFormByOrderNumber' while highlighting* DoCmd.SendObject acSendReport, "rptOrdersByFormByOrderNumber", acFormatSNP, EmailAddress, , , subject, Body, False*
as i click *to send attachment* i see the 'Outlook default email ...' msg i clicked yes but nothing happens and i believe it triggered a mail into the outbox as well.

Thanks a lot OBP - u have been great help!
Now the only thing remaining is can the email address b auto fill from the form as i complete a new company details in a table/form?


----------



## OBP (Mar 8, 2005)

If you look at sending multiple emails you can use that method with a recordset, but if yuo just want to send one, take my Form and add your Company email field to it in place of the email address, or just make it the Control source of that field.

I didn't do anything with the "Send an email" vba Code as I didn't think you would be interested in it.


----------



## huma (Jul 25, 2007)

thanks OBP - its been quite a while that i worked in Access environment. the requirement is to send a mail as the information is completed for a company and the curser moves to enter the next record. Now i cannot show the form to the user. What m i doing is to trigger the mail with an attachment as the record is filled and save record button is pressed. Now can u tell me how to get the email filled in the code from this very record and the attachment is taken from another table from the same db. & then the mail is sent. the only altering information will b the email of the recipient.
Please help out OBP! 
I know u do not give up easily!


----------



## OBP (Mar 8, 2005)

huma, ok you have the emailing part of it. There are 2 ways to do what you want, the first is to open the current emailing form (set to hidden mode, so the user doesn't see it) and pass the Company details to it for inclusion in the email.
The other method uses the VBA Recordset similar to that used in the sending to multiple recipients, except it only opens one record. It takes this format
Dim er As Double, rs As Object, SQL As String
SQL = "SELECT LabourRates.* " & _
"FROM LabourRates " & _
"WHERE Date1 = #" & Format(Me.Date, "mm/dd/yyyy") & "#"
Set rs = CurrentDb.OpenRecordset(SQL)
er = rs.EndUserRate
rs.Close
Set rs = Nothing
Except you want to pick up the Company data instead of matching a date. The syntax for that is different to using dates so I would need the Table Name, the Table field name that Identifies the Company (CompanyID perhaps) and any other table field names that you might want to use, like email address, Contact Name etc. Plus the field on the Form that we will use to match the table field that identifies the current Company.
To open the Attachments table I will need it's name and the name of the field that holds the attachment.


----------



## huma (Jul 25, 2007)

Table name: *Company details
*relevant fields:
_Company ID	
Company Name	
Email	
Contact Person (title)	
Contact Person Name	
_
Attachment table name: *Products*
relevant fields:_
Product ID 
Product name
Product image
Product Catalog
_


----------



## OBP (Mar 8, 2005)

huma, do yuo want to use the Contact Person's name anywhere in the email?
Do you want to send all 3 attachments?
Will they always be entered?
What links the Company Table to the Products table or is the products table your products and not the companies?


----------



## huma (Jul 25, 2007)

No, dont use Contact Person's Name it as i want a general mail to be sent.
Product image , Product Catalog & Product Qoute will be sent - all these 3
these 3 attachmetns will always be there as part of the first mail to every client
Product ID links both tables - 1 record from product table links to many in Company Detail table.


----------



## OBP (Mar 8, 2005)

huma, put this piece of VBA code in your Save Record Button.
Dim rs As Object, SQL As String
SQL = "SELECT Products.* " & _
"FROM Products " & _
"WHERE [Product ID] = '" & Me.[Product ID] & "' "
Set rs = CurrentDb.OpenRecordset(SQL)
msgbox rs![Product image] & " - " & rs![Product Catalog]
rs.Close
Set rs = Nothing

It should open the table with the Product ID from your form's current record and display the data from Product Image and Product Catalog fields.
This may not work due to the Syntax of the Where statement, is very inconsistent.
Where does the Product Quote come from, you didn't list it as being in the Products table


----------



## huma (Jul 25, 2007)

Sorry for missing that out. Product quote is also one of the attachments as 'Product Quote'


----------



## OBP (Mar 8, 2005)

Did you test it as it was, I need to know if the Where statement is going to work.


----------



## huma (Jul 25, 2007)

i added the code to the SAVE button but when i clicked nothing happened.


----------



## OBP (Mar 8, 2005)

Has your button got a VBA "On Error" trap, for showing error messages?
Something should have happened.


----------



## huma (Jul 25, 2007)

_Dim rs As Object, SQL As String
SQL = "SELECT Products.* " & _
"FROM Products " & _
"WHERE [Product ID] = '" & Me.[Product ID] & "' "
Set rs = CurrentDb.OpenRecordset(SQL)
MsgBox rs![Product image] & " - " & rs![Product Catalog]
rs.Close
Set rs = Nothing_
is all i wrote to the SAVE button. Nothing is happening on click

But when i see the Macro window for SAVE button there are 3 lines mentioned and the third one is a condition [MacroError]<>0


----------



## OBP (Mar 8, 2005)

Can you add a line before the SQL = 

On Error Goto errorcatch

and after the Set Rs = Nothing add

exit sub
errorcatch:
MsgBox Err.Description


----------



## huma (Jul 25, 2007)

So now the code becomes:
_Dim rs As Object, SQL As String
On Error GoTo errorcatch

SQL = "SELECT Products.* " & _
"FROM Products " & _
"WHERE [Product ID] = '" & Me.[Product ID] & "' "
Set rs = CurrentDb.OpenRecordset(SQL)
MsgBox rs![Product image] & " - " & rs![Product Catalog]
rs.Close
Set rs = Nothing
Exit Sub
errorcatch:
MsgBox Err.Description
End Sub_

Still nothing happened!


----------



## OBP (Mar 8, 2005)

On what Button Event have you put the code?
After the On error line where you have the space put in

msgbox "1"

and after Set rs = Nothing

msgbox "2"

You should at least get both of those messages if the code is being run.


----------



## huma (Jul 25, 2007)

button event 'on click' 
Now the code becomes:

_Private Sub CmdSave_Click()
Dim rs As Object, SQL As String
On Error GoTo errorcatch
MsgBox "1"

SQL = "SELECT Products.* " & _
"FROM Products " & _
"WHERE [Product ID] = '" & Me.[Product ID] & "' "
Set rs = CurrentDb.OpenRecordset(SQL)
MsgBox rs![Product image] & " - " & rs![Product Catalog]
rs.Close
Set rs = Nothing
MsgBox "2"

Exit Sub
errorcatch:
MsgBox Err.Description
End Sub_

Nothing is happening


----------



## OBP (Mar 8, 2005)

huma, sorry, this just not compute.
Can you post a zipped copy of a database with your form on it please?


----------



## huma (Jul 25, 2007)

its not attaching as the size of .rar file is 2.12MB .
Can i send u through email.


----------



## OBP (Mar 8, 2005)

huma, did you Compact & Repair the Database before zipping it?


----------



## OBP (Mar 8, 2005)

huma, can you send it again in Access 2003 format please?


----------



## OBP (Mar 8, 2005)

huma, are you Ok with what I sent you?


----------



## huma (Jul 25, 2007)

i'm rather confused!


----------



## OBP (Mar 8, 2005)

Huma, isn't it doing what you want?
Or are you confused about how to proceed with the rest of it?


----------



## huma (Jul 25, 2007)

i'm confused abt how to proceed with the rest of it.


----------



## OBP (Mar 8, 2005)

Did you get my email about the Fields Missing from the Table?
We have

Dim rs As Object, SQL As String
On Error GoTo errorcatch

SQL = "SELECT Products.* " & _
"FROM Products " & _
"WHERE [ID] = " & Me.[Product ID]
Set rs = CurrentDb.OpenRecordset(SQL)
MsgBox rs![Product image]
rs.Close
Set rs = Nothing

Exit Sub
Which is showing you the Product Image from the Table, you want to send that as an attachment, so we need to copy the emailing VB code in to the code for the save button.
You either place the code within the Set rs/ rs.close so that it can pick up the table data and add it to the attachements, or you put the Table data in to variables like the strAttach, strAttach1, strAttach2 and have the emailing code after the Set rs = Nothing..

But you need to get the Tables with the correct number fields in and with some data in them to pick up.


----------



## huma (Jul 25, 2007)

ok i try and get back.
thanks


----------



## OBP (Mar 8, 2005)

Huma, if you get stuck just it to me in an email as before and I will fix it for you.


----------

