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

Hi Amol

For example see this page
http://www.rondebruin.nl/mail/folder3/smallmessage.htm



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Amol" wrote in message ups.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

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 -

With HTMLBody this is possible

From
http://www.rondebruin.nl/mail/tips2.htm

If you want to create emails that are formatted you can use HTMLBody (Office 2000 and up) instead of Body.
You can find a lot of WebPages on the internet with more HTML tags examples.

strbody = "<H3<BDear Customer</B</H3" & _
"Please visit this website to download the new version.<br" & _
"Let me know if you have problems.<br" & _
"<A HREF=""http://www.rondebruin.nl/""Ron's Excel Page</A" & _
"<br<br<BThank you</B"

Use .HTMLBody = strbody then instead of .Body = strbody




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Amol" wrote in message oups.com...
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 -

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 -

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 Bruin
http://www.rondebruin.nl/tips.htm


"Amol" wrote in message oups.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 -

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 -

Hi Amol


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,"



Move this part out of the With Outmail block and copy it above

For Each sh In ThisWorkbook.Worksheets


And add .Body = strbody
to the With Outmail block



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Amol" wrote in message oups.com...

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 -

Working Fine

Now Last and final thing

I am saving this macro in a new workbook and the firstsheet of this
workbook is "LookupTable"

But when i open new splited file the macro wont run

what needs to be done


On Aug 5, 3:02 pm, "Ron de Bruin" wrote:
Hi Amol

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,"

Move this part out of the With Outmail block and copy it above

For Each sh In ThisWorkbook.Worksheets

And add .Body = strbody
to the With Outmail block

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm



"Amol" wrote in ooglegroups.com...

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 -- Hide quoted text -

- Show quoted text -

See your other thread

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Amol" wrote in message ups.com...
Working Fine

Now Last and final thing

I am saving this macro in a new workbook and the firstsheet of this
workbook is "LookupTable"

But when i open new splited file the macro wont run

what needs to be done


On Aug 5, 3:02 pm, "Ron de Bruin" wrote:
Hi Amol

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,"

Move this part out of the With Outmail block and copy it above

For Each sh In ThisWorkbook.Worksheets

And add .Body = strbody
to the With Outmail block

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm



"Amol" wrote in ooglegroups.com...

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 -- Hide quoted text -

- Show quoted text -