Results 1 to 6 of 6

Thread: Email attachments from form field with VB

  1. #1

    Thread Starter
    Registered User
    Join Date
    Feb 2015
    Posts
    2

    Unhappy Email attachments from form field with VB

    The person in our company who made these Access forms, etc has since left and we need a few things changed to this database. I'm the most computer literate person here with a 'little' programming knowledge... but I find myself stuck. We need to add a field to attach pictures/documents into a request database.
    The system already takes the information from various fields and enters it into a word document template and sends an email. The word document can stay as-is, but we need the attachments added to the emails.

    This is what i've got so far. It's giving me an error:

    Code:
    Run-time error '91':
    
    Object variable or With block variable not set
    Which highlights this line when I press 'debug':
    Code:
       Com_Attachment = [Forms]![Frm_Complaint_Entry]![Com_Attachment]
    Here is the code. I've done what I can but I just can't get past this wall :banghead:

    Keep in mind that my knowledge of VB is -VERY- limited. All help is greatly appreciated.

    Code:
    Sub SendMessage(Optional AttachmentPath)
       Dim objOutlook As Outlook.Application
       Dim objOutlookMsg As Outlook.MailItem
       Dim objOutlookRecip As Outlook.recipient
       Dim objOutlookAttach As Outlook.Attachment
       Dim recipient As String
       
       Dim Complaint_Id, Date_Received, Com_Title, Com_FName, Com_SName As String
       Dim Com_No, Com_Street, Com_Town, Phone_No, Complaint, Complaint_Type As String
       Dim Location_No, Location_Street, Location_Town, File_Ref, Urgent As String
       Dim Explain, Com_Email As String
       Dim Com_Attachment As Outlook.Attachment
       
            
       ' Get name of recipient from open query
       recipient = [Forms]![Frm_Get_Email_Address]![Email]
       
       'Get Complaint Details
       DoCmd.SelectObject acForm, "Frm_Complaint_Entry"
       Complaint_Id = [Forms]![Frm_Complaint_Entry]![Complaint_Id]
       Date_Received = [Forms]![Frm_Complaint_Entry]![Date_Received]
       Com_Title = [Forms]![Frm_Complaint_Entry]![Com_Title]
       Com_FName = [Forms]![Frm_Complaint_Entry]![Com_FName]
       Com_SName = [Forms]![Frm_Complaint_Entry]![Com_SName]
       Com_No = [Forms]![Frm_Complaint_Entry]![Com_No]
       Com_Street = [Forms]![Frm_Complaint_Entry]![Com_Street]
       Com_Town = [Forms]![Frm_Complaint_Entry]![Com_Town]
       Com_Email = [Forms]![Frm_Complaint_Entry]![Com_Email]
       Phone_No = [Forms]![Frm_Complaint_Entry]![Phone_No]
       Complaint = [Forms]![Frm_Complaint_Entry]![Complaint]
       Complaint_Type = [Forms]![Frm_Complaint_Entry]![Complaint_Type]
       Location_No = [Forms]![Frm_Complaint_Entry]![Location_No]
       Location_Street = [Forms]![Frm_Complaint_Entry]![Location_Street]
       Location_Town = [Forms]![Frm_Complaint_Entry]![Location_Town]
       File_Ref = [Forms]![Frm_Complaint_Entry]![File_Ref]
       Urgent = [Forms]![Frm_Complaint_Entry]![Urgent]
       Com_Attachment = [Forms]![Frm_Complaint_Entry]![Com_Attachment]
    
    
       'Create the body of the message
       Explain = "Complaint Id: " & Complaint_Id & vbCr
       Explain = Explain & "Date Received: " & Date_Received & vbCrLf & vbLf
       Explain = Explain & "Complainant Details: " & vbCrLf
       Explain = Explain & Com_Title & " " & Com_FName & " " & Com_SName & vbCrLf
       Explain = Explain & Com_No & " " & Com_Street & ", " & Com_Town & vbCrLf
       Explain = Explain & "Phone: " & Phone_No & vbCrLf
       Explain = Explain & "Email Address: " & Com_Email & vbCrLf & vbLf
       Explain = Explain & "Complaint Type: " & Complaint_Type & vbCrLf & vbLf
       Explain = Explain & "Complaint Details:" & vbCrLf
       Explain = Explain & Complaint & vbCrLf & vbLf
       Explain = Explain & "Complaint Location: " & Location_No & " " & Location_Street
       Explain = Explain & ", " & Location_Town & vbCrLf & vbLf
       Explain = Explain & "File Ref: " & File_Ref & vbCrLf & vbLf & "Urgent?: "
       If (Urgent = "-1") Then
            Explain = Explain & "Yes"
       Else
            Explain = Explain & "No"
       End If
       
       
       ' Create the Outlook session.
       
       Set objOutlook = CreateObject("Outlook.Application", "localhost")
    
       ' Create the message.
       Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
    
       With objOutlookMsg
          ' Add the To recipient(s) to the message.
    '     Set objOutlookRecip = .Recipients.Add("Nancy Davolio")
          Set objOutlookRecip = .Recipients.Add(recipient)
          objOutlookRecip.Type = olTo
    
          ' Add the CC recipient(s) to the message.
    '      Set objOutlookRecip = .Recipients.Add("Andrew Fuller")
    '      objOutlookRecip.Type = olCC
    
          ' Set the Subject, Body, and Importance of the message.
          .Subject = "Complaints Database - New Assignment : Complaint Id " & Complaint_Id
    '      .Body = "Last test - I promise." & vbCrLf & vbCrLf
          .Body = Explain
          If (Urgent = "-1") Then
            .Importance = olImportanceHigh  'High importance
          End If
          
          ' Add attachments to the message.
          If Not IsMissing(Com_Attachment) Then
             Set objOutlookAttach = .Attachments.Add(Com_Attachment)
          End If
                  
          ' Resolve each Recipient's name.
    '      For Each objOutlookRecip In .Recipients
    '         objOutlookRecip.Resolve
    '         If Not objOutlookRecip.Resolve Then
    '         objOutlookMsg.Display
    '      End If
    '      Next
          .Send
    
       End With
       Set objOutlookMsg = Nothing
       Set objOutlook = Nothing
    End Sub

  2. #2
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,143

    Re: Email attachments from form field with VB

    If you want to add an attachment to the email, look at this: http://www.vbforums.com/showthread.p...ent-using-smtp

  3. #3
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Email attachments from form field with VB

    Dim Com_Attachment As Outlook.Attachment
    i would believe this should be a string as it would be the name of the attachment, not the attachment object, which is later added to the mail item object
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  4. #4

    Thread Starter
    Registered User
    Join Date
    Feb 2015
    Posts
    2

    Re: Email attachments from form field with VB

    Sorry, had a few things on my plate this week, wasn't able to check back. If i change the variable to a string is says that the object doesn't support the property or method when getting the info from the form.

    If I change
    Code:
    [Forms]Com_Attachment = [Forms]![Frm_Complaint_Entry]![Com_Attachment]
    to
    Code:
    [Forms]Com_Attachment = [Forms]![Frm_Complaint_Entry]![Com_Attachment].fileName
    i can get one attachment name (not path). If I was able to get the path as well, I think I could sort out the rest. However that will only be for one attachment - if the user wants to attach multiple files i'll have to sort something else out - maybe some sort of 'for loop' or something.

    Thanks again

  5. #5
    Frenzied Member
    Join Date
    May 2006
    Location
    some place in the cloud
    Posts
    1,886

    Re: Email attachments from form field with VB

    Here's a working piece of code that send mail with attachments using Outlook
    Code:
    	Dim oApp            As Outlook.Application
    	Dim oEmail          As Outlook.MailItem
    
    	Dim MMail           As String
    	Dim MSubject	    As String
    	Dim MMsg	    As String
    	Dim MXMLFile	    As String
    	Dim MPDFFile	    As String
    
    	MMail = "someone@hotmail.com"
    	MSubject = "Subject"
    	MMsg = "---The message goes here---" & VBCrLf & "---The message goes here---"
    	MXMLFile = "C:\xxx\yyy\zzz.xml"
    	MPDFFile = "C:\xxx\yyy\zzz.pdf"
    
    
            Set oApp = New Outlook.Application
            Set oEmail = oApp.CreateItem(olMailItem)
    
            With oEmail
                .To = MMail
                .Subject = MSubject
                .BodyFormat = olFormatPlain
                .Body = MMsg
                .Attachments.Add MXMLFile, olByValue
                .Attachments.Add MPDFFile, olByValue
                .Recipients.ResolveAll
                .Send
            End With
    
            Set oEmail = Nothing
                oApp.Quit
            Set oApp = Nothing
    JG


    ... If your problem is fixed don't forget to mark your threads as resolved using the Thread Tools menu ...

  6. #6
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Email attachments from form field with VB

    maybe some sort of 'for loop' or something.
    is the attachment actually in the database? or does the database contain a path to the attachment? if multiple attachments a loop would be required

    i am not sure how to add a database field to an email as an attachment, may require writing to a temp file first, some googling seems to confirm this in several results
    see http://www.parkersoftware.com/forum/...ail-attachment
    note: you can not then delete or overwrite any attachment file until after the email is sent
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width