| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
auto mailing of selective range as attachment
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
auto mailing of selective range as attachment
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
auto mailing of selective range as attachment
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
auto mailing of selective range as attachment
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" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
auto mailing of selective range as attachment
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(x lCellTypeConstants) 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" wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
auto mailing of selective range as attachment
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(x lCellTypeConstants) 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" wrote in message ... 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
auto mailing of selective range as attachment
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" wrote in message ... 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(x lCellTypeConstants) 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" wrote in message ... 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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
auto mailing of selective range as attachment
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" wrote in message ... 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(x lCellTypeConstants) 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" wrote in message ... 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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
auto mailing of selective range as attachment
You are welcome
-- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Eddy Stan" wrote in message ... 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" wrote in message ... 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(x lCellTypeConstants) 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" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sri NEED HELP - AUTO MAILING THE FILE WHILE CLOSING | Excel Programming | |||
| Selective Mailing Of Attachments | Excel Programming | |||
| Problem mailing as an attachment | New Users to Excel | |||
| Problem mailing as an attachment | Setting up and Configuration of Excel | |||
| mailing whole excel file as an attachment. | Excel Discussion (Misc queries) | |||
Linear Mode
