ExcelBanter - Help with Send e-mail

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Help with Send e-mail (https://www.excelbanter.com/excel-programming/388692-help-send-e-mail.html)

Help with Send e-mail

Hi all, i am not a programme but a dabbler and need help with the following.
I have a spreedsheet were i have a list of names (There could be duplicates)
i then need to take one instance of each name, look up the e-mail address and
send the same e-mail to them all...

Could anybody help me with this please ??
--
Les

Help with Send e-mail

On May 3, 12:46 pm, Les wrote:
Hi all, i am not a programme but a dabbler and need help with the following.
I have a spreedsheet were i have a list of names (There could be duplicates)
i then need to take one instance of each name, look up the e-mail address and
send the same e-mail to them all...

Could anybody help me with this please ??
--
Les

Give us some more info about the layout of your sheet. For instance
where is the email you'll be looking up?

Help with Send e-mail

Please Ignore this thread, i have the answer...

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

Help with Send e-mail

http://www.rondebruin.nl/sendmail.htm

should get you started. I don't recall if it has the lookup the email
address part, but then again it isn't clear where you want it to look up the
email address.

--
Regards,
Tom Ogilvy

"Les" wrote:

Hi all, i am not a programme but a dabbler and need help with the following.
I have a spreedsheet were i have a list of names (There could be duplicates)
i then need to take one instance of each name, look up the e-mail address and
send the same e-mail to them all...

Could anybody help me with this please ??
--
Les

Help with Send e-mail

I have one link to a example on this page Tom
http://www.rondebruin.nl/mail/folder3/message.htm

Tip from Patrick and changed by you and me in 2004
http://www.rondebruin.nl/files/messageunique.txt

--

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

"Tom Ogilvy" wrote in message ...
http://www.rondebruin.nl/sendmail.htm

should get you started. I don't recall if it has the lookup the email
address part, but then again it isn't clear where you want it to look up the
email address.

--
Regards,
Tom Ogilvy

"Les" wrote:

Hi all, i am not a programme but a dabbler and need help with the following.
I have a spreedsheet were i have a list of names (There could be duplicates)
i then need to take one instance of each name, look up the e-mail address and
send the same e-mail to them all...

Could anybody help me with this please ??
--
Les

Help with Send e-mail

Hi Ron, I used your code below but get an error, am i doing something
wrong or a setting perhaps ?

With my cursor on "cell.value" i get the message <Object Variable or
With Block Variable not set

Dim cell As Range
Dim strto As String
On Error Resume Next
For Each cell In ThisWorkbook.Sheets("Sheet1") _
.Range("A1:A10").Cells.SpecialCells(xlCellTypeCons tants)
If cell.Value Like "?*@?*.?*" Then
strto = strto & cell.Value & ";"
End If
Next cell
On Error GoTo 0
If Len(strto) 0 Then strto = Left(strto, Len(strto) - 1)

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

Help with Send e-mail

Post the complete code Les

--

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

"Les Stout" wrote in message ...
Hi Ron, I used your code below but get an error, am i doing something
wrong or a setting perhaps ?

With my cursor on "cell.value" i get the message <Object Variable or
With Block Variable not set

Dim cell As Range
Dim strto As String
On Error Resume Next
For Each cell In ThisWorkbook.Sheets("Sheet1") _
.Range("A1:A10").Cells.SpecialCells(xlCellTypeCons tants)
If cell.Value Like "?*@?*.?*" Then
strto = strto & cell.Value & ";"
End If
Next cell
On Error GoTo 0
If Len(strto) 0 Then strto = Left(strto, Len(strto) - 1)

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

Help with Send e-mail

Hi Ron, herewith is the complete code....

Sub emailToAll()
'
Dim OutApp As Object
Dim OutMail As Object
Dim strto As String, ccAdd As String, Subj As String
Dim BodyText As String, contact As String, comment As String
Dim myComm As Integer, cell As Range
Application.ScreenUpdating = True
'------------------ E-mail address
------------------------------------------------------
On Error Resume Next
For Each cell In ThisWorkbook.Sheets("New gams") _
.Range("T2:T100").Cells.SpecialCells(xlCellTypeCon stants)
If cell.Value Like "?*@?*.?*" Then
strto = strto & cell.Value & ";"
End If
Next cell
On Error GoTo 0
If Len(strto) 0 Then strto = Left(strto, Len(strto) - 1)

'------------------ CC E-mail address
---------------------------------------------------

ccAdd = "DL-ZA-GAMSCC;record kevin, ZA-T-M-22;stout les, ZA-T-M-22"

'------------------ Get the contact persons Surname name
--------------------------------

Subj = "Weekly gAMS Report " & Format(Date, "dd/mm/yy")
With ThisWorkbook.ActiveSheet
BodyText = "Good Day all, " & vbNewLine & vbNewLine & _
"Please find attached the latest gAMS report." &
vbNewLine & vbNewLine & _
" • This report is for new gAMS Documents that
were not created by or allocated to ZA-T-M." & vbNewLine & vbNewLine & _
" • Please open the attachment and refer to the
UPG responsibilities per department on the right of the spreadsheet." &
vbNewLine & vbNewLine & _
" • Then check in the gAMS system to check if it
is valid for you or not, if it is valid for W.9 and you require " &
vbNewLine & _
" funds or an action, you will be required to
contact your CoC or the gAMS Prime Mover to action an AFO." & vbNewLine
& vbNewLine & vbNewLine & vbNewLine & _
"**** Should a UPG be allocated incorrectly or changed,
please advise Les Stout of the changes. ****" & vbNewLine & vbNewLine &
vbNewLine & _
"If you have any queries regarding this document, please
contact the sender." & vbNewLine & vbNewLine & vbNewLine & _
"Best Regards," & vbNewLine & vbNewLine & _
"gAMS_Auto_Macro" & vbNewLine & vbNewLine & _
"ZA-T-M-22" & vbNewLine & vbNewLine & _
"Please Note:" & vbNewLine & _
"The attachment and this e-mail are generated
automatically"
End With
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = strto
.CC = ccAdd
.BCC = ""
.Subject = Subj
.Body = BodyText
.ReadReceiptRequested = True
.Importance = 2
.Attachments.Add ActiveWorkbook.FullName
.Send
End With
Set OutMail = Nothing
Set OutApp = Nothing
chkWkbToCloseGams
End Sub

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

Help with Send e-mail

Strange with a on error resume next above it ?
Do you have the code in your workbook or in the personal.xls

--

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

"Les Stout" wrote in message ...
Hi Ron, herewith is the complete code....

Sub emailToAll()
'
Dim OutApp As Object
Dim OutMail As Object
Dim strto As String, ccAdd As String, Subj As String
Dim BodyText As String, contact As String, comment As String
Dim myComm As Integer, cell As Range
Application.ScreenUpdating = True
'------------------ E-mail address
------------------------------------------------------
On Error Resume Next
For Each cell In ThisWorkbook.Sheets("New gams") _
.Range("T2:T100").Cells.SpecialCells(xlCellTypeCon stants)
If cell.Value Like "?*@?*.?*" Then
strto = strto & cell.Value & ";"
End If
Next cell
On Error GoTo 0
If Len(strto) 0 Then strto = Left(strto, Len(strto) - 1)

'------------------ CC E-mail address
---------------------------------------------------

ccAdd = "DL-ZA-GAMSCC;record kevin, ZA-T-M-22;stout les, ZA-T-M-22"

'------------------ Get the contact persons Surname name
--------------------------------

Subj = "Weekly gAMS Report " & Format(Date, "dd/mm/yy")
With ThisWorkbook.ActiveSheet
BodyText = "Good Day all, " & vbNewLine & vbNewLine & _
"Please find attached the latest gAMS report." &
vbNewLine & vbNewLine & _
" . This report is for new gAMS Documents that
were not created by or allocated to ZA-T-M." & vbNewLine & vbNewLine & _
" . Please open the attachment and refer to the
UPG responsibilities per department on the right of the spreadsheet." &
vbNewLine & vbNewLine & _
" . Then check in the gAMS system to check if it
is valid for you or not, if it is valid for W.9 and you require " &
vbNewLine & _
" funds or an action, you will be required to
contact your CoC or the gAMS Prime Mover to action an AFO." & vbNewLine
& vbNewLine & vbNewLine & vbNewLine & _
"**** Should a UPG be allocated incorrectly or changed,
please advise Les Stout of the changes. ****" & vbNewLine & vbNewLine &
vbNewLine & _
"If you have any queries regarding this document, please
contact the sender." & vbNewLine & vbNewLine & vbNewLine & _
"Best Regards," & vbNewLine & vbNewLine & _
"gAMS_Auto_Macro" & vbNewLine & vbNewLine & _
"ZA-T-M-22" & vbNewLine & vbNewLine & _
"Please Note:" & vbNewLine & _
"The attachment and this e-mail are generated
automatically"
End With
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = strto
.CC = ccAdd
.BCC = ""
.Subject = Subj
.Body = BodyText
.ReadReceiptRequested = True
.Importance = 2
.Attachments.Add ActiveWorkbook.FullName
.Send
End With
Set OutMail = Nothing
Set OutApp = Nothing
chkWkbToCloseGams
End Sub

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

Help with Send e-mail

Hi Ron,sorry for the delay in reply, i have the code in my workbook. The
portion of code that i posted earlier was from your tips below.

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

Best regards,

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

Help with Send e-mail

Hello Ron, as i suspected the error was on my side, i was not pointing
to the workbook where the range was. It is now working 100%, thank
you...

Best regards,

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

Help with Send e-mail

Ok, good to hear that it is working now

--

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

"Les Stout" wrote in message ...
Hello Ron, as i suspected the error was on my side, i was not pointing
to the workbook where the range was. It is now working 100%, thank
you...

Best regards,

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***