bloodmilksky
10-17-2016, 09:23 AM
Hi Guys,
I was just wondering if anyone can help me.
I have a sheet that has alot of named ranges and all of them have the customers AC number at the top of the first column and the email address in the 2nd column (a1:b1,C1:D1,E1:F1....and so on) they all vary in size for example A1:B20 C1:10 but all of the ranges names are the same as the AC Number
I was just wondering if anyone knows how to use VBA to run through Sheet2 and email of each of these named ranges to the email address at the top of the range.
I have asked this question previously but the answer that I was offered keeps erroring with:
Run-Time Error '-2147467259(80004005)':
Method 'MailEnvelope' of object '_worksheet' failed
and highlights this line in the code "With ActiveSheet.MailEnvelope"
Any help would be greatly appreciated.
many thanks
Jamie
Sub Email_Ranges()
Dim rG As Range
Dim RangeToSend As Range
Dim CustomerMail As String
Set rG = ActiveWorkbook.ActiveSheet.[b1]
ActiveWorkbook.EnvelopeVisible = True
Do While rG.Value <> vbNullString
CustomerMail = rG.Value
Set RangeToSend = rG.Offset(, -1).Resize(30, 2)
'With RangeToSend.Parent.MailEnvelope
''Uncomment below if you get an error
rG.Parent.Activate
RangeToSend.Select
With Selection.Parent.MailEnvelope
.Introduction = "Good Morning"
With .Item
.To = CustomerMail
.Subject = "Just testing, sorry for filling your inbox ^_^ "
.display 'to test
.Send 'to send
End With
End With
Debug.Print CustomerMail & " receives " & RangeToSend.Address
Set rG = rG.Offset(, 2)
Loop
ActiveWorkbook.EnvelopeVisible = False
End Sub
Sub Email_Ranges()
Dim rG As Range
Dim RangeToSend As Range
Dim CustomerMail As String
Set rG = ActiveWorkbook.ActiveSheet.[b1]
ActiveWorkbook.EnvelopeVisible = True
Do While rG.Value <> vbNullString
CustomerMail = rG.Value
Set RangeToSend = rG.Offset(, -1).Resize(30, 2)
'With RangeToSend.Parent.MailEnvelope
''Uncomment below if you get an error
rG.Parent.Activate
RangeToSend.Select
With Selection.Parent.MailEnvelope
.Introduction = "Good Morning"
With .Item
.To = CustomerMail
.Subject = "Just testing, sorry for filling your inbox ^_^ "
.display 'to test
.Send 'to send
End With
End With
Debug.Print CustomerMail & " receives " & RangeToSend.Address
Set rG = rG.Offset(, 2)
Loop
ActiveWorkbook.EnvelopeVisible = False
End Sub
I was just wondering if anyone can help me.
I have a sheet that has alot of named ranges and all of them have the customers AC number at the top of the first column and the email address in the 2nd column (a1:b1,C1:D1,E1:F1....and so on) they all vary in size for example A1:B20 C1:10 but all of the ranges names are the same as the AC Number
I was just wondering if anyone knows how to use VBA to run through Sheet2 and email of each of these named ranges to the email address at the top of the range.
I have asked this question previously but the answer that I was offered keeps erroring with:
Run-Time Error '-2147467259(80004005)':
Method 'MailEnvelope' of object '_worksheet' failed
and highlights this line in the code "With ActiveSheet.MailEnvelope"
Any help would be greatly appreciated.
many thanks
Jamie
Sub Email_Ranges()
Dim rG As Range
Dim RangeToSend As Range
Dim CustomerMail As String
Set rG = ActiveWorkbook.ActiveSheet.[b1]
ActiveWorkbook.EnvelopeVisible = True
Do While rG.Value <> vbNullString
CustomerMail = rG.Value
Set RangeToSend = rG.Offset(, -1).Resize(30, 2)
'With RangeToSend.Parent.MailEnvelope
''Uncomment below if you get an error
rG.Parent.Activate
RangeToSend.Select
With Selection.Parent.MailEnvelope
.Introduction = "Good Morning"
With .Item
.To = CustomerMail
.Subject = "Just testing, sorry for filling your inbox ^_^ "
.display 'to test
.Send 'to send
End With
End With
Debug.Print CustomerMail & " receives " & RangeToSend.Address
Set rG = rG.Offset(, 2)
Loop
ActiveWorkbook.EnvelopeVisible = False
End Sub
Sub Email_Ranges()
Dim rG As Range
Dim RangeToSend As Range
Dim CustomerMail As String
Set rG = ActiveWorkbook.ActiveSheet.[b1]
ActiveWorkbook.EnvelopeVisible = True
Do While rG.Value <> vbNullString
CustomerMail = rG.Value
Set RangeToSend = rG.Offset(, -1).Resize(30, 2)
'With RangeToSend.Parent.MailEnvelope
''Uncomment below if you get an error
rG.Parent.Activate
RangeToSend.Select
With Selection.Parent.MailEnvelope
.Introduction = "Good Morning"
With .Item
.To = CustomerMail
.Subject = "Just testing, sorry for filling your inbox ^_^ "
.display 'to test
.Send 'to send
End With
End With
Debug.Print CustomerMail & " receives " & RangeToSend.Address
Set rG = rG.Offset(, 2)
Loop
ActiveWorkbook.EnvelopeVisible = False
End Sub