search for attachment before emailing

[live2ride]

i have a field "spName" and command button "cmdEmailReport"
when i click "cmdEmailReport" i would like to attach 2 files to my email


first file would be the report from access
next thing i would like to search my folder "C:\SP" for file thats like "spName" (field) and attach that file as well
something like "c:\SP\" like "'*" & me.spName &"*'"

i can attach report to email just fine, but my problem is searching for a file in folder with name and attaching that file as well to same email

[dreday]

Sound's like you're using the native email function in access, is this correct? The Access function only allows you to attach one file through the code. Here's a solution I found online yesterday:

Public Function MailTo(ByVal ExportPath As String) As BooleanDim OutlookApp As Object
Dim EM As Object
On Error GoTo ErrorHandler:


Set OutlookApp = GetObject(, "Outlook.Application")
OutlookApp.GetNamespace("MAPI").Logon


Set EM = OutlookApp.CreateItem(0)


With EM
    .To = "you@you.com"
    .Subject = "Data submission for " & Get_Global("program_name")
    .Body = "Thank you!" & vbCrLf & vbCrLf & Get_Global("program_name")
    .Attachments.Add ExportPath
    .Send
End With


MailTo = True


Set OutlookApp = Nothing
Set EM = Nothing


Exit Function


ErrorHandler:
'if outlook is closed, open it
If Err.Number = 429 Then
Set OutlookApp = CreateObject("Outlook.Application")
Resume Next
End If


If Not Err.Number = 287 Then
MsgBox "Error Number: " & Err.Number & " " & Err.Description
End If


Set OutlookApp = Nothing
Set EM = Nothing


End Function

Using that you can add as many attachments as you want with .attachments.add method.

To search though files in a directory use this guide: http://allenbrowne.com/func-11.html

Loop through the names of all of the files somehow and then compare each name as you stated before: "c:\SP\" like "'*" & me.spName &"*'"

I'd recommend using instr() instead of a like statement, my preference

[live2ride]

would i need to save the report to a folder and attach it using the above method as well or is there an easier way something like DoCmd.SendObject acSendReport

[live2ride]

i tried below and its not working, i get compile error: sub or function not defined, sorry im a rookie here
.Attachments.Add FileExists("C:\SP\'*" & Me.spName & "*'")
.Attachments.Add FileExists("C:\SP" & "'*" & Me.spName & "*'")

[dreday]

yes, you would need to save the report. sendobject only allows 1 attachment. Did you add the code for the FileExists() function to your code module? I think that's why it's coming up undefined

[dreday]

also, I don't think you can use that search string in the file exists method, try using the file listing function defined here http://allenbrowne.com/ser-59.html but instead of filling a listbox have the items add to an array

then you can construct a loop like

dim i,x as integer
i=0
x=ubound(array_variable)
for i to x
  if instr(array_variable(i),me.spname) > 0 then 'tests to see if spname is part of the file name
       .attachments.add array_variable(i)
  end if
next i

That will loop through all the names in the array variable and add any of them that match your me.spname var

[live2ride]

im sorry for stupid questions but could you give me an example?

[dreday]

No worries, I'll get back to you when I have some time today

[dreday]

This should do it for you, let me know if I can explain any parts. Basically hitting the button outputs the report and then searches the output folder (whatever folder the database is in) for anything that contains the same name. Good luck!

[live2ride]

you have been a great help , thank you

[dreday]

You're welcome!