Auto Attachment in the email (1 Viewer)

aman · Jul 5, 2018

Hi Guys

I have a 2 column listbox which stores file path and File name of a file. I want to write VBA code so that when save button is clicked then an email gets sent to e.g Test@gmail.com with an attachment of the files stored in the listbpox. So the listbox first column stores Filepath and second column stores FileName. Suppose there are 2 files stored in the listbox then 2 files need to be attached to an email .

Any help will be much appreciated.

Thanks

bastanu · Jul 5, 2018

You need to search the forums for this as there are many similar threads. Basically you need to loop through all items in your listbox (example here how to do that:https://access-programmers.co.uk/forums/showthread.php?t=242192), build the full name of the attachment and use a method (Outlook, CDO) to create your email message and add the attachments to its attachment collection. Look at the bottom of this thread for similar threads.

Post your code here if you get stuck.

Cheers,
Vlad

aman · Jul 6, 2018

I have tried but can't figure it out. Any help would be much appreciated.

Thanks

aman · Jul 6, 2018

I wrote down the following code but I am getting run time error 'Cannot create file , Right click the folder you want to create file in ....." . I just want to attach files to outlook but not sure whats going wrong.

Code:

Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim strEmail As String
Dim strMsg As String
Dim oLook As Object
Dim oMail As Object
Dim rs As Recordset

Set rs = CurrentDb.OpenRecordset("Select FilePath from tbl_RMS_Appeals_ImportDocs where ActivityRef= " & gRef & " and FormRef=" & Fref & "")
Set oLook = CreateObject("Outlook.Application")
Set oLook = CreateObject("Outlook.Application")
Set oMail = oLook.CreateItem(0)
    With oMail
              .To = "RMSSupport@boi.com"
              .Body = "See below the requestor comments " & vbNewLine & Me.txtComments
              .Subject = "Appeal raised"
               If Not (rs.BOF = True And rs.EOF = True) Then
                  Do While Not rs.EOF
                   [COLOR="Red"] .Attachments.Add (rs.Fields(0))[/COLOR]
                  Loop
               End If
                   .Send
    End With

Set oMail = Nothing
Set oLook = Nothing

Gasman · Jul 6, 2018

I think you should have

Code:

[COLOR=Red].Attachments.Add rs.Fields(0)[/COLOR]

or possibly EVAL(rs.Fields(0))

also you should be walking through the recordset?
I would have also tested that we had a record before creating the email?

HTH

Code:

Set rs = CurrentDb.OpenRecordset("Select FilePath from tbl_RMS_Appeals_ImportDocs where ActivityRef= " & gRef & " and FormRef=" & Fref & "")

Set oLook = CreateObject("Outlook.Application")
Set oMail = oLook.CreateItem(0)
    With oMail
              .To = "RMSSupport@boi.com"
              .Body = "See below the requestor comments " & vbNewLine & Me.txtComments
              .Subject = "Appeal raised"
               If Not (rs.BOF And rs.EOF) Then
                  Do While Not rs.EOF
                    .Attachments.Add rs.Fields(0)
                    rs.MoveNext
                  Loop
               End If
               .Send
    End With

Set oMail = Nothing
Set oLook = Nothing
Set rs = Nothing

CJ_London · Jul 6, 2018

and is 'Select FilePath …..' sufficient - according to your first post this is just the filepath. If it doesn't include the filename then it should be

Select Filepath & "" & filename ....

(the "" assumes you haven't already included it in your filepath)

aman · Jul 6, 2018

The filepath column stores both Filepath and FileName . But the code gives me a runtime error '3251' 'Operation is not supported for this type of object ' in the below highlighted line:

Code:

Private Sub email()
 

Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim strEmail As String
Dim strMsg As String
Dim oLook As Object
Dim oMail As Object
Dim rs As Recordset

Set rs = CurrentDb.OpenRecordset("Select FilePath from tbl_RMS_Appeals_ImportDocs where ActivityRef= " & gRef & " and FormRef=" & Fref & "")

Set oLook = CreateObject("Outlook.Application")
Set oMail = oLook.CreateItem(0)
    With oMail
              .To = "RMSSupport@boi.com"
              .Body = "See below the requestor comments " & vbNewLine & Me.txtComments
              .Subject = "Appeal raised"
               If Not (rs.BOF And rs.EOF) Then
                  Do While Not rs.EOF
                  MsgBox rs.Fields(0)
                [COLOR="Red"]    .Attachments.Add rs.Fields(0)[/COLOR]
                    rs.MoveNext
                  Loop
               End If
               .Send
    End With

Set oMail = Nothing
Set oLook = Nothing
Set rs = Nothing
End Sub

Gasman · Jul 6, 2018

So try the EVAL option or the Value property

aman · Jul 6, 2018

Gasman, I tried Eval function and now it gives me runtime error '2482' RMS cannot find the name 'L' you entered in the expression.

Code:

With oMail
              .To = "RMSSupport@boi.com"
              .Body = "See below the requestor comments " & vbNewLine & Me.txtComments
              .Subject = "Appeal raised"
               If Not (rs.BOF And rs.EOF) Then
                  Do While Not rs.EOF
                 ' MsgBox rs.Fields(0)
                    .Attachments.Add Eval(rs.Fields(0))
                    rs.MoveNext
                  Loop
               End If
               .Send
    End With

Now I take off Eval function and replace rs.fields(0) with actual value as below then it works fine. Does it mean I need to add double quotes somewhere around rs.fields(0) ???

Code:

 .Attachments.Add ("L:\Access Databases\Group Manufacturing\Mortgages Direct\Appeal Docs\989601\Icon Set (2).docx")

aman · Jul 6, 2018

Yes its resolved , I changed the following line as below

Code:

 .Attachments.Add """" & rs.Fields(0) &""""

Auto Attachment in the email (1 Viewer)

aman

Registered User.

bastanu

AWF VIP

aman

Registered User.

aman

Registered User.

Gasman

Enthusiastic Amateur

CJ_London

Super Moderator

aman

Registered User.

Gasman

Enthusiastic Amateur

aman

Registered User.

aman

Registered User.

Similar threads

Users who are viewing this thread