Mail Body
Hi Ron
This is the final VBA that i mannage to colate from your guidance but
still the body msg is not appearing on mail.
Please help
Sub Mail_Every_Worksheet()
'Working in 2000-2007
Dim sh As Worksheet
Dim wb As Workbook
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim TempFilePath As String
Dim TempFileName As String
Dim OutApp As Object
Dim OutMail As Object
Dim MailAdress As String
Dim strbody As String
TempFilePath = Environ$("temp") & "\"
If Val(Application.Version) < 12 Then
'You use Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143
Else
'You use Excel 2007
FileExtStr = ".xlsm": FileFormatNum = 52
End If
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
For Each sh In ThisWorkbook.Worksheets
MailAdress = ""
On Error Resume Next
MailAdress =
Application.WorksheetFunction.VLookup(Int(sh.Name) ,
Sheets("LookupTable").Range("A1:B500"), 2, False)
On Error GoTo 0
If MailAdress Like "?*@?*.?*" Then
sh.Copy
Set wb = ActiveWorkbook
TempFileName = "Daily Credit MIS Dt." & " " & Format(Now,
"dd-mmm-yy") & " " & sh.Name
Set OutMail = OutApp.CreateItem(0)
With wb
.SaveAs TempFilePath & TempFileName & FileExtStr,
FileFormat:=FileFormatNum
On Error Resume Next
With OutMail
.To = MailAdress
.CC = ""
.BCC = ""
.Subject = "CMS TRANSACTIONS" & " " & sh.Name
strbody = "Dear All" & vbNewLine & vbNewLine & _
"Please find attached file of Credit/Debit given
to your account on dt" & Format(Now, "dd-mmm-yy") & vbNewLine & _
" " & vbNewLine & _
" " & vbNewLine & _
"Thanks & Regards,"
.Attachments.Add wb.FullName
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.display 'or use .Display
End With
On Error GoTo 0
.Close SaveChanges:=False
End With
Set OutMail = Nothing
Kill TempFilePath & TempFileName & FileExtStr
End If
Next sh
Set OutApp = Nothing
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub
On Aug 5, 1:40 am, "Ron de Bruin" wrote:
You not have to open a file if you want to mail it
But you can use GetOpenFileName
Sub test()
Dim FName As Variant
Dim wb As Workbook
Dim MyPath As String
Dim SaveDriveDir As String
SaveDriveDir = CurDir
MyPath = ThisWorkbook.Path
ChDrive MyPath
ChDir MyPath
FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xls), *.xls")
If FName < False Then
Workbooks.Open (FName)
End If
ChDrive SaveDriveDir
ChDir SaveDriveDir
End Sub
--
Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm
"Amol" wrote in ooglegroups.com...
Ron
As u guided me to create macro to send mail to the various mail id's
there is something more which i want to add
like want to put button on main sheet to ask user to open a desire
file, How would i do that?
On Aug 5, 1:20 am, Amol wrote:
Is it possible to format text size,color and type of the font
On Aug 5, 12:45 am, "Ron de Bruin" wrote:
Hi Amol
For example see this pagehttp://www.rondebruin.nl/mail/folder3/smallmessage.htm
--
Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm
"Amol" wrote in oglegroups.com...
HI
Please guide me abt adding text in next line in mail body of macro
I want to send mails through macro where i put some code in macro
like .body = "Dear All"
Now i want "regards" on next line
thanxs- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -
|