Issue with filling Excel Template cells, save then send on outlook (1 Viewer)

totalnovice2

Registered User.
Local time
Today, 04:48
Joined
May 21, 2013
Messages
36
Hi!

I have found this forum so helpful in the past so I am hoping that this is the place to get help for my new issue too.

I have the code below which takes information from a form on access and sends it over to the correct place on an excel spreadsheet template. This works fine but I then need it to save and send on outlook.

The issue I am having is that the saved document is not attaching to the e-mail. The subject etc all work fine but the excel spreadsheet just doesn't attach. When I go into the folder I have specified for the document to be saved in it isn't there either. :0(

The code for the e-mail "callmail" function works perfectly for word documents but I don't know if it is different for an excel file.

Please help!! :banghead:



Code:
Private Sub Command154_Click()
 
On Error Resume Next
 
    Dim appExcel As Excel.Application
    Dim wbook As Excel.Workbook
    Dim wsheet As Excel.Worksheet
 
    Set appExcel = New Excel.Application
    Set wbook = appExcel.Workbooks.Open("C:\Users\me\desktop\Auto\Access\New Access\Latest\Tmobile & Orange.xltm")
    Set wsheet = wbook.Worksheets("Permit Request Form")
 
    With wsheet
        .Range("F2").Value = Forms![Front Page]![Address #2]
        .Cells(3, 2).Value = Forms![Front Page]![Site 2 Owner]
        .Cells(3, 3).Value = Forms![Front Page]![Site 2 Name]
     .Cells(3, 4).Value = Forms![Front Page]![Postcode S2]
     .Cells(3, 5).Value = Forms![Front Page]![Text98]
     .Cells(3, 6).Value = Forms![Front Page]![Text139]
     .Cells(3, 25).Value = Forms![Front Page]![Combo79] & " " & Forms![Front Page]![Combo81]
 
 
     wbook.activedoc.SaveAs2 FileName:="C:\Users\Public\ Orange Tmob.xls"
Call Mail_Radio_Outlook4("C:\Users\Public\ Orange Tmob.xls")
Set appExcel = Nothing
 
End With
End Sub
 
Function Mail_Radio_Outlook4(activedoc As String)
'Working in Office 2000-2010
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
    Dim acc_req As String
 
 
 
       Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
 
 
    strbody = "Hello.<br> <br> Please Find attached request for access. <br>"
 
   acc_req = "Orange / Tmob Access request" & "  " & [Forms]![Front Page]![Text98].Value & "  " & Forms![Front Page]![Site 2 Name].Value
 
      With OutMail
      On Error Resume Next
 
        .Display
        .To = ""
        .CC = [EMAIL="email@emailaddress.com"]email@emailaddress.com[/EMAIL]
        .Subject = acc_req
        .Attachments.Add (activedoc)
        .HTMLBody = strbody & "<br>" & .HTMLBody
        .Display
 
 
    End With
 
    Set OutMail = Nothing
    Set OutApp = Nothing
 
    MsgBox "Your E-mail has been generated. Please add your climbing certificates", vbInformation + vbOKOnly, "E-mail Sent"
 
 
 
 
 
 
    wbook.Close True
 
 
End Function
 

Hutchy

Registered User.
Local time
Yesterday, 22:48
Joined
Jun 28, 2013
Messages
42
If you want to know why I thanked the OP, it's because that code that he posted helped me :)
 

totalnovice2

Registered User.
Local time
Today, 04:48
Joined
May 21, 2013
Messages
36
Thanks :)

I managed to sort out my issue but can't remember what I did differently!
 

Hutchy

Registered User.
Local time
Yesterday, 22:48
Joined
Jun 28, 2013
Messages
42
That's great news. because no one responded to your request :(

:)
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 04:48
Joined
Jul 9, 2003
Messages
16,378
No one responded to your post because you posted it in 2013 if you had posted it in 2015 you would have got a response.
 

Users who are viewing this thread

Top Bottom