Making a y/n flag check work

R

RedMonkey

New Member
Joined
Apr 27, 2011
Messages
45
Been lurking for a long while then joined and lurked some more. I'm creating and altering exisitng functions at work and it has been kinda fun (finding the code regarding the use of Concatenate on a array from this forum was fantastic) so I've started learning VBA from the beginning.

But this issue is me leaping forward and creating a spliced abomination of code apparently.

The Goal: For the Macro to check the yes/no flag in Col C and if yes, proceed with sending an email with the file attachments that columns D and maybe E will point to. (Each contact is stable and will have different files that are also stable.) The send list changes from time to time and the yes/no flag feels like the simplest control.

I have scripts that send email fine and use the yes/no flag fine for another process but not together. The section in bold is either completly ignored and sends email regardless or when I tinker with it it hangs up with various errors dealing with the control variable already in use or unexpected line ending.

I understand the terms used in VBA but the structure knowledge is the real challenge. (a good portion of my learning was simply recording a macro and finding the unneeded code/comparing it to similar macros)

And advice would be appreciated.

(I really need to invest in excel genie, I believe the ABCDE makes sense since the data order is simple and I can't get it to space correctly for this post.)


A B C D E
<TABLE style="WIDTH: 427pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=568><COLGROUP><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3474" width=95><COL style="WIDTH: 128pt; mso-width-source: userset; mso-width-alt: 6217" width=170><COL style="WIDTH: 40pt; mso-width-source: userset; mso-width-alt: 1938" width=53><COL style="WIDTH: 94pt; mso-width-source: userset; mso-width-alt: 4571" width=125><COL style="WIDTH: 94pt; mso-width-source: userset; mso-width-alt: 4571" width=125><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 71pt; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl75 height=20 width=95>Contact Name</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 128pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl75 width=170>Email</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 40pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl75 width=53>Yes/No</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 94pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl75 width=125>File Location 1</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 94pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl75 width=125>File Location 2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20>John Doe</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl73>Jane.Doe@Unknown.com</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl74>Yes</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl77>System path to file</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl77>System path to file</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20>Jane Doe</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl73>John.Doe@Unknown.com</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl74>No</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl77>System path to file</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl77>System path to file</TD></TR></TBODY></TABLE>


Code: 
Sub Send_Files()
'Working in 2000-2010
Dim OutApp As Object
Dim OutMail As Object
Dim sh As Worksheet
Dim cell As Range, FileCell As Range, rng As Range
 
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
Set sh = Sheets("Mailinfo")
<o:p></o:p>
Set OutApp = CreateObject("Outlook.Application")
 
<o:p></o:p>
[B]'Checks y/n before all other processes<o:p></o:p>[/B]
[B]On Error GoTo cleanup<o:p></o:p>[/B]
[B]For Each cell In Columns("B").Cells.SpecialCells(xlCellTypeConstants)<o:p></o:p>[/B]
[B]If cell.Value Like "?*@?*.?*" And _<o:p></o:p>[/B]
[B]LCase(Cells(cell.Row, "C").Value) = "yes" Then<o:p></o:p>[/B]
[B]End If<o:p></o:p>[/B]
[B]Next cell<o:p></o:p>[/B]
 
For Each cell In sh.Columns("B").Cells.SpecialCells(xlCellTypeConstants)
<o:p></o:p>
'Enter the file names in the D:E column in each row
Set rng = sh.Cells(cell.Row, 1).Range("D1:E1")
<o:p></o:p>
If cell.Value Like "?*@?*.?*" And _
Application.WorksheetFunction.CountA(rng) > 0 Then
Set OutMail = OutApp.CreateItem(0)
<o:p></o:p>
With OutMail
.To = cell.Value
.Subject = "Testfile"
.Body = "Hi " & cell.Offset(0, -1).Value
 
For Each FileCell In rng.SpecialCells(xlCellTypeConstants)
If Trim(FileCell) <> "" Then
If Dir(FileCell.Value) <> "" Then
.Attachments.Add FileCell.Value
End If
End If
Next FileCell
 
.Send 'Or use Display
End With
<o:p></o:p>
Set OutMail = Nothing
End If
Next cell
<o:p></o:p>
Set OutApp = Nothing
<o:p></o:p>
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
 
cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True
<o:p></o:p>
End Sub
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Sort by date Sort by votes
Try this:

Code: 
Sub Send_Files()
'Working in 2000-2010
Dim OutApp As Object
Dim OutMail As Object
Dim sh As Worksheet
Dim cell As Range, FileCell As Range, rng As Range
 
With Application
.EnableEvents = False
.ScreenUpdating = False
End With

Set sh = Sheets("Mailinfo")

Set OutApp = CreateObject("Outlook.Application")
 

'Checks y/n before all other processes
On Error GoTo cleanup
For Each cell In Columns("B").Cells.SpecialCells(xlCellTypeConstants)
If cell.Value Like "?*@?*.?*" And _
LCase(Cells(cell.Row, "C").Value) = "yes" Then

'End If - Not required
'Next cell - Not required
 
'For Each cell In sh.Columns("B").Cells.SpecialCells(xlCellTypeConstants) - Not required

'Enter the file names in the D:E column in each row
Set rng = sh.Cells(cell.Row, 1).Range("D1:E1")

If cell.Value Like "?*@?*.?*" And _
Application.WorksheetFunction.CountA(rng) > 0 Then
Set OutMail = OutApp.CreateItem(0)

With OutMail
.To = cell.Value
.Subject = "Testfile"
.Body = "Hi " & cell.Offset(0, -1).Value
 
For Each FileCell In rng.SpecialCells(xlCellTypeConstants)
If Trim(FileCell) <> "" Then
If Dir(FileCell.Value) <> "" Then
.Attachments.Add FileCell.Value
End If
End If
Next FileCell
 
.Send 'Or use Display
End With

Set OutMail = Nothing
End If
Next cell

Set OutApp = Nothing

With Application
.EnableEvents = True
.ScreenUpdating = True
End With
 
cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True

End Sub
 
Upvote 0
Comfy said:
Try this:

Code: 
Sub Send_Files()
 
.Send 'Or use Display
End With
 
Set OutMail = Nothing
End If
[B][COLOR=sandybrown]Next cell[/COLOR][/B]
 
Set OutApp = Nothing
 
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
 
cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True
 
End Sub
Click to expand...

The colored item of the last sections throws a Next without For error. I get where this is going and will update if I work it out as well.
 
Upvote 0
Slight update for the missing End if:

Code: 
Sub Send_Files()
'Working in 2000-2010
Dim OutApp As Object
Dim OutMail As Object
Dim sh As Worksheet
Dim cell As Range, FileCell As Range, rng As Range
 
With Application
.EnableEvents = False
.ScreenUpdating = False
End With

Set sh = Sheets("Mailinfo")

Set OutApp = CreateObject("Outlook.Application")
 

'Checks y/n before all other processes
On Error GoTo cleanup
For Each cell In Columns("B").Cells.SpecialCells(xlCellTypeConstants)
If cell.Value Like "?*@?*.?*" And _
LCase(Cells(cell.Row, "C").Value) = "yes" Then

'End If - Not required
'Next cell - Not required
 
'For Each cell In sh.Columns("B").Cells.SpecialCells(xlCellTypeConstants) - Not required

'Enter the file names in the D:E column in each row
Set rng = sh.Cells(cell.Row, 1).Range("D1:E1")

If cell.Value Like "?*@?*.?*" And _
Application.WorksheetFunction.CountA(rng) > 0 Then
Set OutMail = OutApp.CreateItem(0)

With OutMail
.To = cell.Value
.Subject = "Testfile"
.Body = "Hi " & cell.Offset(0, -1).Value
 
For Each FileCell In rng.SpecialCells(xlCellTypeConstants)
If Trim(FileCell) <> "" Then
If Dir(FileCell.Value) <> "" Then
.Attachments.Add FileCell.Value
End If
End If
Next FileCell
 
.Send 'Or use Display
End With
End If
Set OutMail = Nothing
End If
Next cell

Set OutApp = Nothing

With Application
.EnableEvents = True
.ScreenUpdating = True
End With
 
cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True

End Sub
 
Upvote 0
Absolutely perfect, I was on the trail of IFs END IFS, I actually learned something!

Now it works perfectly after I stick the standardized email body back in (I got to figure out HTMLBody and tags earlier).

Thanks for your help!
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
624
smozgur
smozgur
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
E
  • Question Question
VBA code clean up
Replies
9
Views
583
mumps
mumps
E
  • Question Question
Adding a custom message.
Replies
1
Views
666
Micron
M
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G

Forum statistics

Threads
1,224,609
Messages
6,179,879
Members
452,948
Latest member
Dupuhini

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top