totalnovice2
Registered User.
- Local time
- Today, 15:28
- 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:
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