title: Macro emailing all but two worksheets
body: <p>I have a workbook that contains sheets of price lists for different customers, and each week I have to email all the pricelists to the corresponding customers. This is a fairly time-consuming task and I have been trying to automate it with VBA. For the most part, I have succeeded by using Ron de Bruin's code but I have run into an issue that I can't seem to solve so I'm for hoping some insight as to where I've gone wrong.</p>
  
  <p>As previously mentioned this workbook contains multiple, different, price sheets that all need to be sent to different customers. I have modified <a href="https://www.rondebruin.nl/win/s1/outlook/bmail2.htm" rel="nofollow noreferrer">this</a> code slightly to meet my needs (e.g. only coping visible cells, to include the email signature, etc...). One major change I made to this code is that I loop through a range that contains the recipients' addresses (which can be seen below).</p>
  
  <p>The problem that I'm currently facing is that this code works for all but two sheets. It will create an email for the two problem sheets, but nothing in the range (A1:L85) will be pasted into the email - it just sends an email with no body besides my signature. What makes this worse (or more interesting) is that these two problem sheets occur in the "middle" of the worksheets. Let's say problem sheet 1 = PS_1 and problem sheet 2 = PS_2 it would be like this:</p>
  
  <p>WS_1, WS_2, ..., WS_14, PS_1, WS_16, PS_2, WS_18, ..., WS_32</p>
  
  <p>So I'm wondering why it's only messing up on these two sheets, and how to fix it.</p>
  
  <p>I have included all my code below (except for RangetoHTML which is on Ron de Bruin's website, and a function to that gets the worksheet names):</p>
  
  <pre><code>Sub email()
  ' this is intended to speed up the code
  With Excel.Application
          .ScreenUpdating = False
          .Calculation = Excel.xlCalculationManual
          .EnableEvents = False
  End With
  
  Dim OutApp As Object
  Dim OutMail As Object
  
  Dim rng As Range 'this is the range of the price list
  Dim erng As Range 'this is the range of email addresses
  Dim cell As Range
  
  Dim wsnames() As String 'worksheet names are stored in an array
  Dim pricedate As String 'the week of prices the user provides (e.g. July 1st - July 7th)
  Dim tsheets As Integer 'total sheets
  
  'counting variables
  Dim m As Integer
  Dim n As Integer
  
  Set OutApp = CreateObject("Outlook.Application")
  
  'initializing variables
  Set rng = Nothing
  
  'initializing variables
  n = 0
  
  pricedate = InputBox("Enter the week the prices are for (e.g. July 10th - July 15th): ", "Week")
  
  If pricedate = vbNullString Then Exit Sub 'if the user presses cancel it will stop the macro
  
  tsheets = ActiveWorkbook.Worksheets.Count 'finds how many sheets are in the workbook to adjust the size of the array
  
  ReDim wsnames(tsheets) 'resizes the size of the array
  
  wsnames = storewsnames 'passing the sheet names to wsnames
  
  For m = 1 To tsheets - 1
  
      If wsnames(m) = "Atwood" Then Exit For 'looks for the index of worksheet "Atwood", and once it's found it exits the loop
  
  Next m
  
  For n = m To tsheets - 1 'sets n to the index of "Atwood"
  
      If Sheets(wsnames(n)).Visible = True Then 'only will send emails to visible sheets
  
          With Sheets(wsnames(n))
  
              Set rng = .Range("A1:L85")
  
              Set erng = .Range("M71:M85")
  
          End With
  
          On Error GoTo cleanup
  
          For Each cell In erng 'searches the cells in the email addresses range
  
              If cell.Value Like "?*@?*.?*" Then 'looks
              '_for email addresses where the email addresses are saved
  
                  Set OutMail = OutApp.CreateItem(0)
  
                  On Error Resume Next
  
                   With OutMail
  
                      .Display
                      .To = ""
                      .CC = ""
                      .BCC = cell.Value
                      .Subject = "CM Weekly Prices - " &amp; wsnames(n)
                      .HTMLBody = "&lt;BODY style=font-size:11pt;font-family:Calibri&gt; Hi, " &amp; _
                          "&lt;br&gt;&lt;br&gt;" &amp; "Below are the prices for the week of " &amp; pricedate &amp; _
                          "." &amp; RangetoHTML(rng) &amp; "Thank you, &lt;/BODY&gt;&lt;br&gt;" &amp; .HTMLBody
                      .Send
  
                  End With
  
                  On Error GoTo cleanup
                  Set OutMail = Nothing
  
              End If
  
          Next cell
  
      End If
  
  Next n
  
  ' this is intended to speed up the code
  With Excel.Application
          .ScreenUpdating = True
          .Calculation = Excel.xlCalculationAutomatic
          .EnableEvents = True
  End With
  
  cleanup:
      Set OutApp = Nothing
      Application.ScreenUpdating = True
  
  End Sub
  </code></pre>
  
  <p>I'm very unfamiliar with using VBA to send an email so I have been heavily reliant on the code I have used and have tried to only make minor-ish changes.</p>
  
  <p>If there is anything else you folks need, or something is unclear please let me know! </p>