Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
email to each person with attachment
Dear all,
I have excel spreadsheet: In column A the names of the people In column B the e-mail Addresses In column C the Filenames like this C:\Data\Book2.xls This don't have to be Excel files. I have the Macro wich loop through each row in Sheet1 and if there is a E-mail address and a filename that exist in that row it will create a mail with this information and send it. Sub TestFile() Dim olApp As Outlook.Application Dim olMail As MailItem Dim cell As Range Application.ScreenUpdating = False Set olApp = New Outlook.Application For Each cell In Sheets("Sheet1").Columns("B").Cells.SpecialCells(x lCellTypeConstants) If cell.Offset(0, 1).Value < "" Then If cell.Value Like "*@*" And Dir(cell.Offset(0, 1).Value) < "" Then Set olMail = olApp.CreateItem(olMailItem) With olMail .To = cell.Value .Subject = "Testfile" .Body = "Hi " & cell.Offset(0, -1).Value .Attachments.Add cell.Offset(0, 1).Value .Send 'Or use Display End With Set olMail = Nothing End If End If Next cell Set olApp = Nothing Application.ScreenUpdating = True End Sub Problem is that for each sending of e-mail it is necessary confirmation and I have to press button "YES". How I can avoid pressing on "YES" and to send everything automatically. Best wishes, Lado |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
email to each person with attachment
See this part of my site
http://www.rondebruin.nl/sendmail.htm#Prevent -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Kiknadze Lado" wrote in message ... Dear all, I have excel spreadsheet: In column A the names of the people In column B the e-mail Addresses In column C the Filenames like this C:\Data\Book2.xls This don't have to be Excel files. I have the Macro wich loop through each row in Sheet1 and if there is a E-mail address and a filename that exist in that row it will create a mail with this information and send it. Sub TestFile() Dim olApp As Outlook.Application Dim olMail As MailItem Dim cell As Range Application.ScreenUpdating = False Set olApp = New Outlook.Application For Each cell In Sheets("Sheet1").Columns("B").Cells.SpecialCells(x lCellTypeConstants) If cell.Offset(0, 1).Value < "" Then If cell.Value Like "*@*" And Dir(cell.Offset(0, 1).Value) < "" Then Set olMail = olApp.CreateItem(olMailItem) With olMail .To = cell.Value .Subject = "Testfile" .Body = "Hi " & cell.Offset(0, -1).Value .Attachments.Add cell.Offset(0, 1).Value .Send 'Or use Display End With Set olMail = Nothing End If End If Next cell Set olApp = Nothing Application.ScreenUpdating = True End Sub Problem is that for each sending of e-mail it is necessary confirmation and I have to press button "YES". How I can avoid pressing on "YES" and to send everything automatically. Best wishes, Lado |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
email to each person with attachment
Thank you for your answer.
I'm using MS Outlook and I couldn't find how to uncheck this option. Best, Lado "Ron de Bruin" wrote in message ... See this part of my site http://www.rondebruin.nl/sendmail.htm#Prevent -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Kiknadze Lado" wrote in message ... Dear all, I have excel spreadsheet: In column A the names of the people In column B the e-mail Addresses In column C the Filenames like this C:\Data\Book2.xls This don't have to be Excel files. I have the Macro wich loop through each row in Sheet1 and if there is a E-mail address and a filename that exist in that row it will create a mail with this information and send it. Sub TestFile() Dim olApp As Outlook.Application Dim olMail As MailItem Dim cell As Range Application.ScreenUpdating = False Set olApp = New Outlook.Application For Each cell In Sheets("Sheet1").Columns("B").Cells.SpecialCells(x lCellTypeConstants) If cell.Offset(0, 1).Value < "" Then If cell.Value Like "*@*" And Dir(cell.Offset(0, 1).Value) < "" Then Set olMail = olApp.CreateItem(olMailItem) With olMail .To = cell.Value .Subject = "Testfile" .Body = "Hi " & cell.Offset(0, -1).Value .Attachments.Add cell.Offset(0, 1).Value .Send 'Or use Display End With Set olMail = Nothing End If End If Next cell Set olApp = Nothing Application.ScreenUpdating = True End Sub Problem is that for each sending of e-mail it is necessary confirmation and I have to press button "YES". How I can avoid pressing on "YES" and to send everything automatically. Best wishes, Lado |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
email to each person with attachment
Read good!!
For Outlook see this webpage http://www.dimastr.com/redemption/ OR Express ClickYes http://www.express-soft.com/mailmate/clickyes.html -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Kiknadze Lado" wrote in message ... Thank you for your answer. I'm using MS Outlook and I couldn't find how to uncheck this option. Best, Lado "Ron de Bruin" wrote in message ... See this part of my site http://www.rondebruin.nl/sendmail.htm#Prevent -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Kiknadze Lado" wrote in message ... Dear all, I have excel spreadsheet: In column A the names of the people In column B the e-mail Addresses In column C the Filenames like this C:\Data\Book2.xls This don't have to be Excel files. I have the Macro wich loop through each row in Sheet1 and if there is a E-mail address and a filename that exist in that row it will create a mail with this information and send it. Sub TestFile() Dim olApp As Outlook.Application Dim olMail As MailItem Dim cell As Range Application.ScreenUpdating = False Set olApp = New Outlook.Application For Each cell In Sheets("Sheet1").Columns("B").Cells.SpecialCells(x lCellTypeConstants) If cell.Offset(0, 1).Value < "" Then If cell.Value Like "*@*" And Dir(cell.Offset(0, 1).Value) < "" Then Set olMail = olApp.CreateItem(olMailItem) With olMail .To = cell.Value .Subject = "Testfile" .Body = "Hi " & cell.Offset(0, -1).Value .Attachments.Add cell.Offset(0, 1).Value .Send 'Or use Display End With Set olMail = Nothing End If End If Next cell Set olApp = Nothing Application.ScreenUpdating = True End Sub Problem is that for each sending of e-mail it is necessary confirmation and I have to press button "YES". How I can avoid pressing on "YES" and to send everything automatically. Best wishes, Lado |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
email to each person with attachment
Thank you Ron,
I did already and it works but I don't know person who will use this program want's to install "express clickYES" program. I will try to find out with him. Thank you for your help, Lado "Ron de Bruin" wrote in message ... Read good!! For Outlook see this webpage http://www.dimastr.com/redemption/ OR Express ClickYes http://www.express-soft.com/mailmate/clickyes.html -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Kiknadze Lado" wrote in message ... Thank you for your answer. I'm using MS Outlook and I couldn't find how to uncheck this option. Best, Lado "Ron de Bruin" wrote in message ... See this part of my site http://www.rondebruin.nl/sendmail.htm#Prevent -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Kiknadze Lado" wrote in message ... Dear all, I have excel spreadsheet: In column A the names of the people In column B the e-mail Addresses In column C the Filenames like this C:\Data\Book2.xls This don't have to be Excel files. I have the Macro wich loop through each row in Sheet1 and if there is a E-mail address and a filename that exist in that row it will create a mail with this information and send it. Sub TestFile() Dim olApp As Outlook.Application Dim olMail As MailItem Dim cell As Range Application.ScreenUpdating = False Set olApp = New Outlook.Application For Each cell In Sheets("Sheet1").Columns("B").Cells.SpecialCells(x lCellTypeConstants) If cell.Offset(0, 1).Value < "" Then If cell.Value Like "*@*" And Dir(cell.Offset(0, 1).Value) < "" Then Set olMail = olApp.CreateItem(olMailItem) With olMail .To = cell.Value .Subject = "Testfile" .Body = "Hi " & cell.Offset(0, -1).Value .Attachments.Add cell.Offset(0, 1).Value .Send 'Or use Display End With Set olMail = Nothing End If End If Next cell Set olApp = Nothing Application.ScreenUpdating = True End Sub Problem is that for each sending of e-mail it is necessary confirmation and I have to press button "YES". How I can avoid pressing on "YES" and to send everything automatically. Best wishes, Lado |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sending a Spreadsheet as an Email Attachment vs. Imbedded in Email | Excel Discussion (Misc queries) | |||
Email Attachment | Excel Discussion (Misc queries) | |||
How to send attachment to one person at a time w/ routing slip? | Excel Discussion (Misc queries) | |||
send wkbk as an email attachment with an email address copied from | Excel Discussion (Misc queries) |