SaveAs function and Send mail in VBA
 

Hi,

I found the below coding, (which I am very greatful for, thank Tim)
however, I can't seem to get it working after the few amendments I have

made. In my mind I see two steps I need help on. The save as function
that works in another workbook, but not this one. I would like to have
the SaveAs box open already in the Root drive/folder of the office,
allowing the user to then chose which sub folder they wish. Second step

it to the send the attachment with the new name as the subject, then
include a standard response in the body?


I hope this all makes sense... it is 230 am and I am seeing squares,
just can't get this last bit working....


Many Thanks in advance.


Craigy


Sub Send_Mail_Test()
' Courtesy Tim Zych - 08/09/2000
Dim ol As Object
Dim mailitem As Object


Set ol = CreateObject("Outlook.Application")
Set mailitem = ol.CreateItem(olMailItem)
Set wb = ActiveWorkbook
With wb
SaveAsName = ("E Billing" & " " & "A" & Range("L6").Text & " " &
"Consumption" & " " & Range("L8").Text & " " & Range("A30").Text)
..SaveAs = SaveAsName


With mailitem
.To = ""
.CC = ""
.Subject = SaveAsName
.Body = "Please find attached your E Billing for this month."


.Attachments.Add wb.FullName
.Display
' .Send
End With


Set ol = Nothing
Set mailitem = Nothing


End Sub

SaveAs function and Send mail in VBA
 

Hi, I have got this sorted now.

Thanks
C
wrote:
Hi,

I found the below coding, (which I am very greatful for, thank Tim)
however, I can't seem to get it working after the few amendments I have

made. In my mind I see two steps I need help on. The save as function
that works in another workbook, but not this one. I would like to have
the SaveAs box open already in the Root drive/folder of the office,
allowing the user to then chose which sub folder they wish. Second step

it to the send the attachment with the new name as the subject, then
include a standard response in the body?


I hope this all makes sense... it is 230 am and I am seeing squares,
just can't get this last bit working....


Many Thanks in advance.


Craigy


Sub Send_Mail_Test()
' Courtesy Tim Zych - 08/09/2000
Dim ol As Object
Dim mailitem As Object


Set ol = CreateObject("Outlook.Application")
Set mailitem = ol.CreateItem(olMailItem)
Set wb = ActiveWorkbook
With wb
SaveAsName = ("E Billing" & " " & "A" & Range("L6").Text & " " &
"Consumption" & " " & Range("L8").Text & " " & Range("A30").Text)
.SaveAs = SaveAsName


With mailitem
.To = ""
.CC = ""
.Subject = SaveAsName
.Body = "Please find attached your E Billing for this month."


.Attachments.Add wb.FullName
.Display
' .Send
End With


Set ol = Nothing
Set mailitem = Nothing


End Sub

SaveAs function and Send mail in VBA
 

Hi Craigy

See the VBA help for GetSaveAsFilename

See my site for a lot of code and post back
http://www.rondebruin.nl/sendmail.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl



wrote in message oups.com...
Hi,

I found the below coding, (which I am very greatful for, thank Tim)
however, I can't seem to get it working after the few amendments I have

made. In my mind I see two steps I need help on. The save as function
that works in another workbook, but not this one. I would like to have
the SaveAs box open already in the Root drive/folder of the office,
allowing the user to then chose which sub folder they wish. Second step

it to the send the attachment with the new name as the subject, then
include a standard response in the body?


I hope this all makes sense... it is 230 am and I am seeing squares,
just can't get this last bit working....


Many Thanks in advance.


Craigy


Sub Send_Mail_Test()
' Courtesy Tim Zych - 08/09/2000
Dim ol As Object
Dim mailitem As Object


Set ol = CreateObject("Outlook.Application")
Set mailitem = ol.CreateItem(olMailItem)
Set wb = ActiveWorkbook
With wb
SaveAsName = ("E Billing" & " " & "A" & Range("L6").Text & " " &
"Consumption" & " " & Range("L8").Text & " " & Range("A30").Text)
.SaveAs = SaveAsName


With mailitem
.To = ""
.CC = ""
.Subject = SaveAsName
.Body = "Please find attached your E Billing for this month."


.Attachments.Add wb.FullName
.Display
' .Send
End With


Set ol = Nothing
Set mailitem = Nothing


End Sub