Hi

I am trying to send automail to various persons of various departments from
finance, please help me. As it is voluminous data and repeated task. Let me
explain what I am doing
I have 2 sheets
1 detail data sheet – with autofilter ( I need to filter location code wise,
copy & paste in new work book and send as attachment to various people)
2 mailing information

Data in sheet 2 mailing information
Range a – Sl No
Range b – LocationCode
Range c – Location Name
Range d – Names (names of persons whose id is in range
Range e – To IDS
Range f - CC IDs
Range g – Instructions to type as body in each letter – customized based on
data, which I will type and keep in advance
Range h – Send / ignore – to validate before sending mail, choose to send
mail if the cell has word “Send”
Range I - after sending mail, should store as “Sent” or “Skipped” (when
range h is changed to Send from ignore then mail need to be sent)

The macro should run automatically till the end of list in sheet 2 (70
locations approx), Auto mail is checked in Outlook so I can confirm manually,
bypass that checking is not available.

Can this be automated, please answer me by monday

hi
see this site...
http://www.rondebruin.nl/sendmail.htm

Regards
FSt1

"Eddy Stan" wrote:

> Hi
>
> I am trying to send automail to various persons of various departments from
> finance, please help me. As it is voluminous data and repeated task. Let me
> explain what I am doing
> I have 2 sheets
> 1 detail data sheet – with autofilter ( I need to filter location code wise,
> copy & paste in new work book and send as attachment to various people)
> 2 mailing information
>
> Data in sheet 2 mailing information
> Range a – Sl No
> Range b – LocationCode
> Range c – Location Name
> Range d – Names (names of persons whose id is in range
> Range e – To IDS
> Range f - CC IDs
> Range g – Instructions to type as body in each letter – customized based on
> data, which I will type and keep in advance
> Range h – Send / ignore – to validate before sending mail, choose to send
> mail if the cell has word “Send”
> Range I - after sending mail, should store as “Sent” or “Skipped” (when
> range h is changed to Send from ignore then mail need to be sent)
>
> The macro should run automatically till the end of list in sheet 2 (70
> locations approx), Auto mail is checked in Outlook so I can confirm manually,
> bypass that checking is not available.
>
> Can this be automated, please answer me by monday
>

HI
CAN I USE RANGE NAME AS CREDITNOTE INSTEAD OF A1:K50
HOW CAN I COPY THE RANGE TO NEW SHEET AND SAVE THAT AS AN ATTACHMENT FILE
ANY HELP SPECIFIC

"FSt1" wrote:

> hi
> see this site...
> http://www.rondebruin.nl/sendmail.htm
>
> Regards
> FSt1
>
> "Eddy Stan" wrote:
>
> > Hi
> >
> > I am trying to send automail to various persons of various departments from
> > finance, please help me. As it is voluminous data and repeated task. Let me
> > explain what I am doing
> > I have 2 sheets
> > 1 detail data sheet – with autofilter ( I need to filter location code wise,
> > copy & paste in new work book and send as attachment to various people)
> > 2 mailing information
> >
> > Data in sheet 2 mailing information
> > Range a – Sl No
> > Range b – LocationCode
> > Range c – Location Name
> > Range d – Names (names of persons whose id is in range
> > Range e – To IDS
> > Range f - CC IDs
> > Range g – Instructions to type as body in each letter – customized based on
> > data, which I will type and keep in advance
> > Range h – Send / ignore – to validate before sending mail, choose to send
> > mail if the cell has word “Send”
> > Range I - after sending mail, should store as “Sent” or “Skipped” (when
> > range h is changed to Send from ignore then mail need to be sent)
> >
> > The macro should run automatically till the end of list in sheet 2 (70
> > locations approx), Auto mail is checked in Outlook so I can confirm manually,
> > bypass that checking is not available.
> >
> > Can this be automated, please answer me by monday
> >

I have a body example here
http://www.rondebruin.nl/mail/folder3/row2.htm

I can help you to change it to attachment if this is what you are looking for

--

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


"Eddy Stan" <(E-Mail Removed)> wrote in message news:C8EE7E82-6417-4881-AABC-(E-Mail Removed)...
> HI
> CAN I USE RANGE NAME AS CREDITNOTE INSTEAD OF A1:K50
> HOW CAN I COPY THE RANGE TO NEW SHEET AND SAVE THAT AS AN ATTACHMENT FILE
> ANY HELP SPECIFIC
>
> "FSt1" wrote:
>
>> hi
>> see this site...
>> http://www.rondebruin.nl/sendmail.htm
>>
>> Regards
>> FSt1
>>
>> "Eddy Stan" wrote:
>>
>> > Hi
>> >
>> > I am trying to send automail to various persons of various departments from
>> > finance, please help me. As it is voluminous data and repeated task. Let me
>> > explain what I am doing
>> > I have 2 sheets
>> > 1 detail data sheet – with autofilter ( I need to filter location code wise,
>> > copy & paste in new work book and send as attachment to various people)
>> > 2 mailing information
>> >
>> > Data in sheet 2 mailing information
>> > Range a – Sl No
>> > Range b – LocationCode
>> > Range c – Location Name
>> > Range d – Names (names of persons whose id is in range
>> > Range e – To IDS
>> > Range f - CC IDs
>> > Range g – Instructions to type as body in each letter – customized based on
>> > data, which I will type and keep in advance
>> > Range h – Send / ignore – to validate before sending mail, choose to send
>> > mail if the cell has word “Send”
>> > Range I - after sending mail, should store as “Sent” or “Skipped” (when
>> > range h is changed to Send from ignore then mail need to be sent)
>> >
>> > The macro should run automatically till the end of list in sheet 2 (70
>> > locations approx), Auto mail is checked in Outlook so I can confirm manually,
>> > bypass that checking is not available.
>> >
>> > Can this be automated, please answer me by monday
>> >

Hi
I already have modified your coding to send auto mail with with 1 line
subject and now i want to attach file, the file name i will input in one
column. i need code to pick that file from default directory. I have code as
below

Sub TestFile()
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range



Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon



On Error GoTo cleanup
For Each cell In
Sheets("Sheet1").Columns("B").Cells.SpecialCells(xlCellTypeConstants)
If cell.Value Like "?*@?*.?*" And LCase(cell.Offset(0, 1).Value) =
"yes" Then
Set OutMail = OutApp.CreateItem(0)



On Error Resume Next
With OutMail
.To = cell.Value
.Subject = cell.Offset(0, 3).Value
.Body = "Dear " & cell.Offset(0, -1).Value & vbNewLine &
vbNewLine & _
cell.Offset(0, 2).Value
' "Please contact us to discuss bringing your account
up to date"
'You can add files also like this
'.Attachments.Add ("C:\test.txt")
.Send 'Or use Display
End With

On Error GoTo 0


Set OutMail = Nothing
End If
Next cell


cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub



"Ron de Bruin" wrote:

> I have a body example here
> http://www.rondebruin.nl/mail/folder3/row2.htm
>
> I can help you to change it to attachment if this is what you are looking for
>
> --
>
> Regards Ron de Bruin
> http://www.rondebruin.nl/tips.htm
>
>
> "Eddy Stan" <(E-Mail Removed)> wrote in message news:C8EE7E82-6417-4881-AABC-(E-Mail Removed)...
> > HI
> > CAN I USE RANGE NAME AS CREDITNOTE INSTEAD OF A1:K50
> > HOW CAN I COPY THE RANGE TO NEW SHEET AND SAVE THAT AS AN ATTACHMENT FILE
> > ANY HELP SPECIFIC
> >
> > "FSt1" wrote:
> >
> >> hi
> >> see this site...
> >> http://www.rondebruin.nl/sendmail.htm
> >>
> >> Regards
> >> FSt1
> >>
> >> "Eddy Stan" wrote:
> >>
> >> > Hi
> >> >
> >> > I am trying to send automail to various persons of various departments from
> >> > finance, please help me. As it is voluminous data and repeated task. Let me
> >> > explain what I am doing
> >> > I have 2 sheets
> >> > 1 detail data sheet – with autofilter ( I need to filter location code wise,
> >> > copy & paste in new work book and send as attachment to various people)
> >> > 2 mailing information
> >> >
> >> > Data in sheet 2 mailing information
> >> > Range a – Sl No
> >> > Range b – LocationCode
> >> > Range c – Location Name
> >> > Range d – Names (names of persons whose id is in range
> >> > Range e – To IDS
> >> > Range f - CC IDs
> >> > Range g – Instructions to type as body in each letter – customized based on
> >> > data, which I will type and keep in advance
> >> > Range h – Send / ignore – to validate before sending mail, choose to send
> >> > mail if the cell has word “Send”
> >> > Range I - after sending mail, should store as “Sent” or “Skipped” (when
> >> > range h is changed to Send from ignore then mail need to be sent)
> >> >
> >> > The macro should run automatically till the end of list in sheet 2 (70
> >> > locations approx), Auto mail is checked in Outlook so I can confirm manually,
> >> > bypass that checking is not available.
> >> >
> >> > Can this be automated, please answer me by monday
> >> >
>
>

Hi,
My mail is going but it is not picking the attachment, can u help please
' Set rng = sh.Cells(cell.Row, 1).Range("C1:Z1")
Set rng = sh.Cells(cell.Row, 1).Range("F3:H5")
i have names of file for attaching with full path (is it necesary for full
path ?)
attachments can be 1 or 2 or 3 or even 5 max
so how should i set the above line.

thank you.

"Eddy Stan" wrote:

> Hi
> I already have modified your coding to send auto mail with with 1 line
> subject and now i want to attach file, the file name i will input in one
> column. i need code to pick that file from default directory. I have code as
> below
>
> Sub TestFile()
> Dim OutApp As Object
> Dim OutMail As Object
> Dim cell As Range
>
>
>
> Application.ScreenUpdating = False
> Set OutApp = CreateObject("Outlook.Application")
> OutApp.Session.Logon
>
>
>
> On Error GoTo cleanup
> For Each cell In
> Sheets("Sheet1").Columns("B").Cells.SpecialCells(xlCellTypeConstants)
> If cell.Value Like "?*@?*.?*" And LCase(cell.Offset(0, 1).Value) =
> "yes" Then
> Set OutMail = OutApp.CreateItem(0)
>
>
>
> On Error Resume Next
> With OutMail
> .To = cell.Value
> .Subject = cell.Offset(0, 3).Value
> .Body = "Dear " & cell.Offset(0, -1).Value & vbNewLine &
> vbNewLine & _
> cell.Offset(0, 2).Value
> ' "Please contact us to discuss bringing your account
> up to date"
> 'You can add files also like this
> '.Attachments.Add ("C:\test.txt")
> .Send 'Or use Display
> End With
>
> On Error GoTo 0
>
>
> Set OutMail = Nothing
> End If
> Next cell
>
>
> cleanup:
> Set OutApp = Nothing
> Application.ScreenUpdating = True
> End Sub
>
>
>
> "Ron de Bruin" wrote:
>
> > I have a body example here
> > http://www.rondebruin.nl/mail/folder3/row2.htm
> >
> > I can help you to change it to attachment if this is what you are looking for
> >
> > --
> >
> > Regards Ron de Bruin
> > http://www.rondebruin.nl/tips.htm
> >
> >
> > "Eddy Stan" <(E-Mail Removed)> wrote in message news:C8EE7E82-6417-4881-AABC-(E-Mail Removed)...
> > > HI
> > > CAN I USE RANGE NAME AS CREDITNOTE INSTEAD OF A1:K50
> > > HOW CAN I COPY THE RANGE TO NEW SHEET AND SAVE THAT AS AN ATTACHMENT FILE
> > > ANY HELP SPECIFIC
> > >
> > > "FSt1" wrote:
> > >
> > >> hi
> > >> see this site...
> > >> http://www.rondebruin.nl/sendmail.htm
> > >>
> > >> Regards
> > >> FSt1
> > >>
> > >> "Eddy Stan" wrote:
> > >>
> > >> > Hi
> > >> >
> > >> > I am trying to send automail to various persons of various departments from
> > >> > finance, please help me. As it is voluminous data and repeated task. Let me
> > >> > explain what I am doing
> > >> > I have 2 sheets
> > >> > 1 detail data sheet – with autofilter ( I need to filter location code wise,
> > >> > copy & paste in new work book and send as attachment to various people)
> > >> > 2 mailing information
> > >> >
> > >> > Data in sheet 2 mailing information
> > >> > Range a – Sl No
> > >> > Range b – LocationCode
> > >> > Range c – Location Name
> > >> > Range d – Names (names of persons whose id is in range
> > >> > Range e – To IDS
> > >> > Range f - CC IDs
> > >> > Range g – Instructions to type as body in each letter – customized based on
> > >> > data, which I will type and keep in advance
> > >> > Range h – Send / ignore – to validate before sending mail, choose to send
> > >> > mail if the cell has word “Send”
> > >> > Range I - after sending mail, should store as “Sent” or “Skipped” (when
> > >> > range h is changed to Send from ignore then mail need to be sent)
> > >> >
> > >> > The macro should run automatically till the end of list in sheet 2 (70
> > >> > locations approx), Auto mail is checked in Outlook so I can confirm manually,
> > >> > bypass that checking is not available.
> > >> >
> > >> > Can this be automated, please answer me by monday
> > >> >
> >
> >

Hi Eddy

Start with this example
http://www.rondebruin.nl/mail/folder2/files.htm

--

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


"Eddy Stan" <(E-Mail Removed)> wrote in message news:6696F797-FF95-496E-ACB7-(E-Mail Removed)...
> Hi,
> My mail is going but it is not picking the attachment, can u help please
> ' Set rng = sh.Cells(cell.Row, 1).Range("C1:Z1")
> Set rng = sh.Cells(cell.Row, 1).Range("F3:H5")
> i have names of file for attaching with full path (is it necesary for full
> path ?)
> attachments can be 1 or 2 or 3 or even 5 max
> so how should i set the above line.
>
> thank you.
>
> "Eddy Stan" wrote:
>
>> Hi
>> I already have modified your coding to send auto mail with with 1 line
>> subject and now i want to attach file, the file name i will input in one
>> column. i need code to pick that file from default directory. I have code as
>> below
>>
>> Sub TestFile()
>> Dim OutApp As Object
>> Dim OutMail As Object
>> Dim cell As Range
>>
>>
>>
>> Application.ScreenUpdating = False
>> Set OutApp = CreateObject("Outlook.Application")
>> OutApp.Session.Logon
>>
>>
>>
>> On Error GoTo cleanup
>> For Each cell In
>> Sheets("Sheet1").Columns("B").Cells.SpecialCells(xlCellTypeConstants)
>> If cell.Value Like "?*@?*.?*" And LCase(cell.Offset(0, 1).Value) =
>> "yes" Then
>> Set OutMail = OutApp.CreateItem(0)
>>
>>
>>
>> On Error Resume Next
>> With OutMail
>> .To = cell.Value
>> .Subject = cell.Offset(0, 3).Value
>> .Body = "Dear " & cell.Offset(0, -1).Value & vbNewLine &
>> vbNewLine & _
>> cell.Offset(0, 2).Value
>> ' "Please contact us to discuss bringing your account
>> up to date"
>> 'You can add files also like this
>> '.Attachments.Add ("C:\test.txt")
>> .Send 'Or use Display
>> End With
>>
>> On Error GoTo 0
>>
>>
>> Set OutMail = Nothing
>> End If
>> Next cell
>>
>>
>> cleanup:
>> Set OutApp = Nothing
>> Application.ScreenUpdating = True
>> End Sub
>>
>>
>>
>> "Ron de Bruin" wrote:
>>
>> > I have a body example here
>> > http://www.rondebruin.nl/mail/folder3/row2.htm
>> >
>> > I can help you to change it to attachment if this is what you are looking for
>> >
>> > --
>> >
>> > Regards Ron de Bruin
>> > http://www.rondebruin.nl/tips.htm
>> >
>> >
>> > "Eddy Stan" <(E-Mail Removed)> wrote in message news:C8EE7E82-6417-4881-AABC-(E-Mail Removed)...
>> > > HI
>> > > CAN I USE RANGE NAME AS CREDITNOTE INSTEAD OF A1:K50
>> > > HOW CAN I COPY THE RANGE TO NEW SHEET AND SAVE THAT AS AN ATTACHMENT FILE
>> > > ANY HELP SPECIFIC
>> > >
>> > > "FSt1" wrote:
>> > >
>> > >> hi
>> > >> see this site...
>> > >> http://www.rondebruin.nl/sendmail.htm
>> > >>
>> > >> Regards
>> > >> FSt1
>> > >>
>> > >> "Eddy Stan" wrote:
>> > >>
>> > >> > Hi
>> > >> >
>> > >> > I am trying to send automail to various persons of various departments from
>> > >> > finance, please help me. As it is voluminous data and repeated task. Let me
>> > >> > explain what I am doing
>> > >> > I have 2 sheets
>> > >> > 1 detail data sheet – with autofilter ( I need to filter location code wise,
>> > >> > copy & paste in new work book and send as attachment to various people)
>> > >> > 2 mailing information
>> > >> >
>> > >> > Data in sheet 2 mailing information
>> > >> > Range a – Sl No
>> > >> > Range b – LocationCode
>> > >> > Range c – Location Name
>> > >> > Range d – Names (names of persons whose id is in range
>> > >> > Range e – To IDS
>> > >> > Range f - CC IDs
>> > >> > Range g – Instructions to type as body in each letter – customized based on
>> > >> > data, which I will type and keep in advance
>> > >> > Range h – Send / ignore – to validate before sending mail, choose to send
>> > >> > mail if the cell has word “Send”
>> > >> > Range I - after sending mail, should store as “Sent” or “Skipped” (when
>> > >> > range h is changed to Send from ignore then mail need to be sent)
>> > >> >
>> > >> > The macro should run automatically till the end of list in sheet 2 (70
>> > >> > locations approx), Auto mail is checked in Outlook so I can confirm manually,
>> > >> > bypass that checking is not available.
>> > >> >
>> > >> > Can this be automated, please answer me by monday
>> > >> >
>> >
>> >

Hi Ron,
thank you and the example worked well.
You saved lot of time.
Eddy

"Ron de Bruin" wrote:

> Hi Eddy
>
> Start with this example
> http://www.rondebruin.nl/mail/folder2/files.htm
>
> --
>
> Regards Ron de Bruin
> http://www.rondebruin.nl/tips.htm
>
>
> "Eddy Stan" <(E-Mail Removed)> wrote in message news:6696F797-FF95-496E-ACB7-(E-Mail Removed)...
> > Hi,
> > My mail is going but it is not picking the attachment, can u help please
> > ' Set rng = sh.Cells(cell.Row, 1).Range("C1:Z1")
> > Set rng = sh.Cells(cell.Row, 1).Range("F3:H5")
> > i have names of file for attaching with full path (is it necesary for full
> > path ?)
> > attachments can be 1 or 2 or 3 or even 5 max
> > so how should i set the above line.
> >
> > thank you.
> >
> > "Eddy Stan" wrote:
> >
> >> Hi
> >> I already have modified your coding to send auto mail with with 1 line
> >> subject and now i want to attach file, the file name i will input in one
> >> column. i need code to pick that file from default directory. I have code as
> >> below
> >>
> >> Sub TestFile()
> >> Dim OutApp As Object
> >> Dim OutMail As Object
> >> Dim cell As Range
> >>
> >>
> >>
> >> Application.ScreenUpdating = False
> >> Set OutApp = CreateObject("Outlook.Application")
> >> OutApp.Session.Logon
> >>
> >>
> >>
> >> On Error GoTo cleanup
> >> For Each cell In
> >> Sheets("Sheet1").Columns("B").Cells.SpecialCells(xlCellTypeConstants)
> >> If cell.Value Like "?*@?*.?*" And LCase(cell.Offset(0, 1).Value) =
> >> "yes" Then
> >> Set OutMail = OutApp.CreateItem(0)
> >>
> >>
> >>
> >> On Error Resume Next
> >> With OutMail
> >> .To = cell.Value
> >> .Subject = cell.Offset(0, 3).Value
> >> .Body = "Dear " & cell.Offset(0, -1).Value & vbNewLine &
> >> vbNewLine & _
> >> cell.Offset(0, 2).Value
> >> ' "Please contact us to discuss bringing your account
> >> up to date"
> >> 'You can add files also like this
> >> '.Attachments.Add ("C:\test.txt")
> >> .Send 'Or use Display
> >> End With
> >>
> >> On Error GoTo 0
> >>
> >>
> >> Set OutMail = Nothing
> >> End If
> >> Next cell
> >>
> >>
> >> cleanup:
> >> Set OutApp = Nothing
> >> Application.ScreenUpdating = True
> >> End Sub
> >>
> >>
> >>
> >> "Ron de Bruin" wrote:
> >>
> >> > I have a body example here
> >> > http://www.rondebruin.nl/mail/folder3/row2.htm
> >> >
> >> > I can help you to change it to attachment if this is what you are looking for
> >> >
> >> > --
> >> >
> >> > Regards Ron de Bruin
> >> > http://www.rondebruin.nl/tips.htm
> >> >
> >> >
> >> > "Eddy Stan" <(E-Mail Removed)> wrote in message news:C8EE7E82-6417-4881-AABC-(E-Mail Removed)...
> >> > > HI
> >> > > CAN I USE RANGE NAME AS CREDITNOTE INSTEAD OF A1:K50
> >> > > HOW CAN I COPY THE RANGE TO NEW SHEET AND SAVE THAT AS AN ATTACHMENT FILE
> >> > > ANY HELP SPECIFIC
> >> > >
> >> > > "FSt1" wrote:
> >> > >
> >> > >> hi
> >> > >> see this site...
> >> > >> http://www.rondebruin.nl/sendmail.htm
> >> > >>
> >> > >> Regards
> >> > >> FSt1
> >> > >>
> >> > >> "Eddy Stan" wrote:
> >> > >>
> >> > >> > Hi
> >> > >> >
> >> > >> > I am trying to send automail to various persons of various departments from
> >> > >> > finance, please help me. As it is voluminous data and repeated task. Let me
> >> > >> > explain what I am doing
> >> > >> > I have 2 sheets
> >> > >> > 1 detail data sheet – with autofilter ( I need to filter location code wise,
> >> > >> > copy & paste in new work book and send as attachment to various people)
> >> > >> > 2 mailing information
> >> > >> >
> >> > >> > Data in sheet 2 mailing information
> >> > >> > Range a – Sl No
> >> > >> > Range b – LocationCode
> >> > >> > Range c – Location Name
> >> > >> > Range d – Names (names of persons whose id is in range
> >> > >> > Range e – To IDS
> >> > >> > Range f - CC IDs
> >> > >> > Range g – Instructions to type as body in each letter – customized based on
> >> > >> > data, which I will type and keep in advance
> >> > >> > Range h – Send / ignore – to validate before sending mail, choose to send
> >> > >> > mail if the cell has word “Send”
> >> > >> > Range I - after sending mail, should store as “Sent” or “Skipped” (when
> >> > >> > range h is changed to Send from ignore then mail need to be sent)
> >> > >> >
> >> > >> > The macro should run automatically till the end of list in sheet 2 (70
> >> > >> > locations approx), Auto mail is checked in Outlook so I can confirm manually,
> >> > >> > bypass that checking is not available.
> >> > >> >
> >> > >> > Can this be automated, please answer me by monday
> >> > >> >
> >> >
> >> >
>
>

You are welcome

--

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


"Eddy Stan" <(E-Mail Removed)> wrote in message news:AB3A089F-8A6E-414D-A4E0-(E-Mail Removed)...
> Hi Ron,
> thank you and the example worked well.
> You saved lot of time.
> Eddy
>
> "Ron de Bruin" wrote:
>
>> Hi Eddy
>>
>> Start with this example
>> http://www.rondebruin.nl/mail/folder2/files.htm
>>
>> --
>>
>> Regards Ron de Bruin
>> http://www.rondebruin.nl/tips.htm
>>
>>
>> "Eddy Stan" <(E-Mail Removed)> wrote in message news:6696F797-FF95-496E-ACB7-(E-Mail Removed)...
>> > Hi,
>> > My mail is going but it is not picking the attachment, can u help please
>> > ' Set rng = sh.Cells(cell.Row, 1).Range("C1:Z1")
>> > Set rng = sh.Cells(cell.Row, 1).Range("F3:H5")
>> > i have names of file for attaching with full path (is it necesary for full
>> > path ?)
>> > attachments can be 1 or 2 or 3 or even 5 max
>> > so how should i set the above line.
>> >
>> > thank you.
>> >
>> > "Eddy Stan" wrote:
>> >
>> >> Hi
>> >> I already have modified your coding to send auto mail with with 1 line
>> >> subject and now i want to attach file, the file name i will input in one
>> >> column. i need code to pick that file from default directory. I have code as
>> >> below
>> >>
>> >> Sub TestFile()
>> >> Dim OutApp As Object
>> >> Dim OutMail As Object
>> >> Dim cell As Range
>> >>
>> >>
>> >>
>> >> Application.ScreenUpdating = False
>> >> Set OutApp = CreateObject("Outlook.Application")
>> >> OutApp.Session.Logon
>> >>
>> >>
>> >>
>> >> On Error GoTo cleanup
>> >> For Each cell In
>> >> Sheets("Sheet1").Columns("B").Cells.SpecialCells(xlCellTypeConstants)
>> >> If cell.Value Like "?*@?*.?*" And LCase(cell.Offset(0, 1).Value) =
>> >> "yes" Then
>> >> Set OutMail = OutApp.CreateItem(0)
>> >>
>> >>
>> >>
>> >> On Error Resume Next
>> >> With OutMail
>> >> .To = cell.Value
>> >> .Subject = cell.Offset(0, 3).Value
>> >> .Body = "Dear " & cell.Offset(0, -1).Value & vbNewLine &
>> >> vbNewLine & _
>> >> cell.Offset(0, 2).Value
>> >> ' "Please contact us to discuss bringing your account
>> >> up to date"
>> >> 'You can add files also like this
>> >> '.Attachments.Add ("C:\test.txt")
>> >> .Send 'Or use Display
>> >> End With
>> >>
>> >> On Error GoTo 0
>> >>
>> >>
>> >> Set OutMail = Nothing
>> >> End If
>> >> Next cell
>> >>
>> >>
>> >> cleanup:
>> >> Set OutApp = Nothing
>> >> Application.ScreenUpdating = True
>> >> End Sub
>> >>
>> >>
>> >>
>> >> "Ron de Bruin" wrote:
>> >>
>> >> > I have a body example here
>> >> > http://www.rondebruin.nl/mail/folder3/row2.htm
>> >> >
>> >> > I can help you to change it to attachment if this is what you are looking for
>> >> >
>> >> > --
>> >> >
>> >> > Regards Ron de Bruin
>> >> > http://www.rondebruin.nl/tips.htm
>> >> >
>> >> >
>> >> > "Eddy Stan" <(E-Mail Removed)> wrote in message news:C8EE7E82-6417-4881-AABC-(E-Mail Removed)...
>> >> > > HI
>> >> > > CAN I USE RANGE NAME AS CREDITNOTE INSTEAD OF A1:K50
>> >> > > HOW CAN I COPY THE RANGE TO NEW SHEET AND SAVE THAT AS AN ATTACHMENT FILE
>> >> > > ANY HELP SPECIFIC
>> >> > >
>> >> > > "FSt1" wrote:
>> >> > >
>> >> > >> hi
>> >> > >> see this site...
>> >> > >> http://www.rondebruin.nl/sendmail.htm
>> >> > >>
>> >> > >> Regards
>> >> > >> FSt1
>> >> > >>
>> >> > >> "Eddy Stan" wrote:
>> >> > >>
>> >> > >> > Hi
>> >> > >> >
>> >> > >> > I am trying to send automail to various persons of various departments from
>> >> > >> > finance, please help me. As it is voluminous data and repeated task. Let me
>> >> > >> > explain what I am doing
>> >> > >> > I have 2 sheets
>> >> > >> > 1 detail data sheet – with autofilter ( I need to filter location code wise,
>> >> > >> > copy & paste in new work book and send as attachment to various people)
>> >> > >> > 2 mailing information
>> >> > >> >
>> >> > >> > Data in sheet 2 mailing information
>> >> > >> > Range a – Sl No
>> >> > >> > Range b – LocationCode
>> >> > >> > Range c – Location Name
>> >> > >> > Range d – Names (names of persons whose id is in range
>> >> > >> > Range e – To IDS
>> >> > >> > Range f - CC IDs
>> >> > >> > Range g – Instructions to type as body in each letter – customized based on
>> >> > >> > data, which I will type and keep in advance
>> >> > >> > Range h – Send / ignore – to validate before sending mail, choose to send
>> >> > >> > mail if the cell has word “Send”
>> >> > >> > Range I - after sending mail, should store as “Sent” or “Skipped” (when
>> >> > >> > range h is changed to Send from ignore then mail need to be sent)
>> >> > >> >
>> >> > >> > The macro should run automatically till the end of list in sheet 2 (70
>> >> > >> > locations approx), Auto mail is checked in Outlook so I can confirm manually,
>> >> > >> > bypass that checking is not available.
>> >> > >> >
>> >> > >> > Can this be automated, please answer me by monday
>> >> > >> >
>> >> >
>> >> >
>>
>>