N
nicolascap
Hello experts,
I have an excel table with 4 columns:
- Column 1 contains either nothing, "To", or "Cc" as email destination
fields
- Column 2 contains different email adresses
- Column 3 is empty and is named "To"
- Column 4 is empty and is named "Cc".
Under the first column "email destination field " (containing To, Cc or
no value), I want to add a button that will read through its totality
and then add the corresponding email adress to the "To" Column if a
"To" is read or to the "Cc" Column if a "Cc" is read, and of course do
nothing if nothing is mentionned.
I will then use the following macro to go through the "To" and "Cc"
Columns and create a blank email with the correct email adresses in the
"To" or "Cc"fields:
--------------------------------------------------------------------------
Sub SendWithAtt()
Dim olApp As Outlook.Application
Dim olMail As MailItem
Dim CurrFile As String
Set olApp = New Outlook.Application
Set olMail = olApp.CreateItem(olMailItem)
ActiveWorkbook.Save
CurrFile = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name
With olMail
..To = strto
..CC = "(e-mail address removed)"
..Subject = "These two files"
..Body = ActiveSheet.Range("D4").Text & vbCrLf
..Attachments.Add CurrFile
..Attachments.Add "C:\Documents and Settings\THB3NI\My
Documents\Contact ist example.zip"
..Display '.Send
End With
Set olMail = Nothing
Set olApp = Nothing
End Sub
Private Function strto()
Dim cell As Range
'Dim strto As String
For Each cell In
ThisWorkbook.Sheets("tabelle1").Columns("I").Cells.SpecialCells(xlCellTypeConstants)
If cell.EntireRow.Hidden = False And cell.Value Like "*@*"
Then
strto = strto & cell.Value & ";"
End If
Next
strto = Left(strto, Len(strto) - 1)
--------------------------------------------------------------------------
Attached hereafter the Excel example of my problem
Thanks for your help
Nico
+-------------------------------------------------------------------+
|Filename: Contact Example.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4449 |
+-------------------------------------------------------------------+
I have an excel table with 4 columns:
- Column 1 contains either nothing, "To", or "Cc" as email destination
fields
- Column 2 contains different email adresses
- Column 3 is empty and is named "To"
- Column 4 is empty and is named "Cc".
Under the first column "email destination field " (containing To, Cc or
no value), I want to add a button that will read through its totality
and then add the corresponding email adress to the "To" Column if a
"To" is read or to the "Cc" Column if a "Cc" is read, and of course do
nothing if nothing is mentionned.
I will then use the following macro to go through the "To" and "Cc"
Columns and create a blank email with the correct email adresses in the
"To" or "Cc"fields:
--------------------------------------------------------------------------
Sub SendWithAtt()
Dim olApp As Outlook.Application
Dim olMail As MailItem
Dim CurrFile As String
Set olApp = New Outlook.Application
Set olMail = olApp.CreateItem(olMailItem)
ActiveWorkbook.Save
CurrFile = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name
With olMail
..To = strto
..CC = "(e-mail address removed)"
..Subject = "These two files"
..Body = ActiveSheet.Range("D4").Text & vbCrLf
..Attachments.Add CurrFile
..Attachments.Add "C:\Documents and Settings\THB3NI\My
Documents\Contact ist example.zip"
..Display '.Send
End With
Set olMail = Nothing
Set olApp = Nothing
End Sub
Private Function strto()
Dim cell As Range
'Dim strto As String
For Each cell In
ThisWorkbook.Sheets("tabelle1").Columns("I").Cells.SpecialCells(xlCellTypeConstants)
If cell.EntireRow.Hidden = False And cell.Value Like "*@*"
Then
strto = strto & cell.Value & ";"
End If
Next
strto = Left(strto, Len(strto) - 1)
--------------------------------------------------------------------------
Attached hereafter the Excel example of my problem
Thanks for your help
Nico
+-------------------------------------------------------------------+
|Filename: Contact Example.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4449 |
+-------------------------------------------------------------------+