View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
Helmut Helmut is offline
external usenet poster
 
Posts: 111
Default filename in "Subject" in email

Hi I have:

Dim Fname As Variant

Fname = Application.GetSaveAsFilename("c:\MESSER\" &
Range("mesnum").Value & "_" & Replace(Range("filedate").Value, "/", "") &
".csv")

This works well, then: note the .Subject line...I am trying to use the Fname
from above in the Subject line. How is that possible? right now I get the
rest but not the Fname

Sub Mail_Selection_Outlook_Body()
' You must add a reference to the Microsoft outlook Library
' Don't forget to copy the function RangetoHTML in the module.

Dim sh As Worksheet
Dim rng As Range
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem

'If you know the sheet/range then use this two lines
Set sh = Sheets("START")
Set rng = sh.Range("head")

Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.To = "
.CC = ""
.BCC = "
.Subject = Fname & "_" & " now is in \\Cav_New\Files"
.HTMLBody = RangetoHTML(sh, rng)
.Send 'or use .Display
End With
Set OutMail = Nothing
Set OutApp = Nothing
Application.ScreenUpdating = True