SaveAs function and Send mail in VBA

[[email protected]]

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

[[email protected]]

Hi, I have got this sorted now.

Thanks
C
[email protected] 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

[Ron de Bruin]

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



<[email protected]> wrote in message news:[email protected]...
> 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
>