emails from excel cells

[Abdulkader Bhanpurawala via OfficeKB.com]

hi!

I have a spreadsheet (form) where I have three to four individuals to whom
email should go upon completion of form. These group of individuals change
everytime depending upon selection in the form. Using range cell value, I
could send email to one of them, but not to all, therefore I need help how to
select few cells where I have email ID's. Thanks in advance

 

[Michael Bauer]

Am Sun, 28 Aug 2005 07:47:17 GMT schrieb Abdulkader Bhanpurawala via
OfficeKB.com:

If you´re able to get the content of one cell where is the problem to
get the content of another cell, too? It´s the same syntax, just an
other address.

 

[Abdulkader Bhanpurawala via OfficeKB.com]

Am Sun, 28 Aug 2005 07:47:17 GMT schrieb Abdulkader Bhanpurawala via
OfficeKB.com:

If you´re able to get the content of one cell where is the problem to
get the content of another cell, too? It´s the same syntax, just an
other address.

hi!

[quoted text clipped - 3 lines]

could send email to one of them, but not to all, therefore I need help how to
select few cells where I have email ID's. Thanks in advance

hi!

Please see below the macro I wrote, second cell reference is not working may
be u will be able to figure it out what is going wrong. As u will c below
that email Id in cell "B64" is only working, but I have email id in cell
"F64"

Dim myrange As Range
Set myrange = Worksheets("PO Template").Range("A15,B23:B27,D3538,A42,B42,
C42,D42,G42,G23:G27,H35:H39,D55")
If Application.WorksheetFunction.CountA(myrange) < myrange.Cells.Count Then
MsgBox "fill in all required details"
Exit Sub
End If
Sheets("TRACKING FILE").Select
Range("A11").Select
Selection.Copy
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("A4").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=
_
xlNone, SkipBlanks:=False, Transpose:=False
ActiveWorkbook.Save
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.To = "[email protected]"
.CC = Range("B64", "F64").Value
.Subject = "Approved PO"
.Body = "Please find attached the PO as requested by you. Please liaise
with the supplier for the delivery of the goods or service. I have mailed the
PO to the supplier.. Thanks!"
.Attachments.Add ActiveWorkbook.FullName
.Send
End With
Set OutMail = Nothing
Set OutApp = Nothing



End Sub

 

[Michael Bauer]

Am Tue, 30 Aug 2005 12:02:29 GMT schrieb Abdulkader Bhanpurawala via
OfficeKB.com:

So many code and you´re not able to read the content of one cell, or to join
two strings? Really, that´s unbelievable.

Please read the Excel VBA topics for the Range object and Value property.

You can join two (and more, of course) strings with the & operator. Between
two addresses there must be a semicolon. So it could look like this:

Dim result$
result="a" & ";" & "b"


--
Viele Gruesse / Best regards
Michael Bauer - MVP Outlook

Am Sun, 28 Aug 2005 07:47:17 GMT schrieb Abdulkader Bhanpurawala via
OfficeKB.com:

If you´re able to get the content of one cell where is the problem to
get the content of another cell, too? It´s the same syntax, just an
other address.

hi!

[quoted text clipped - 3 lines]

could send email to one of them, but not to all, therefore I need help how to
select few cells where I have email ID's. Thanks in advance

hi!

Please see below the macro I wrote, second cell reference is not working may
be u will be able to figure it out what is going wrong. As u will c below
that email Id in cell "B64" is only working, but I have email id in cell
"F64"

Dim myrange As Range
Set myrange = Worksheets("PO Template").Range("A15,B23:B27,D3538,A42,B42,
C42,D42,G42,G23:G27,H35:H39,D55")
If Application.WorksheetFunction.CountA(myrange) < myrange.Cells.Count Then
MsgBox "fill in all required details"
Exit Sub
End If
Sheets("TRACKING FILE").Select
Range("A11").Select
Selection.Copy
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("A4").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=
_
xlNone, SkipBlanks:=False, Transpose:=False
ActiveWorkbook.Save
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.To = "[email protected]"
.CC = Range("B64", "F64").Value
.Subject = "Approved PO"
.Body = "Please find attached the PO as requested by you. Please liaise
with the supplier for the delivery of the goods or service. I have mailed the
PO to the supplier.. Thanks!"
.Attachments.Add ActiveWorkbook.FullName
.Send
End With
Set OutMail = Nothing
Set OutApp = Nothing



End Sub