Macro to create 2 tables depending on the date

[teekayy]

Hi All,

I have macro which creates a table based on imported data and attaches this table to an email. Basically what i want to do is adjust the macro to create 2 tables, 1 table to contain all the data where column B is equal to yesterdays date, and another table for all the other dates.

here is the code:

Sub mail_send()
 Application.Calculation = xlCalculationAutomatic  
Dim X As Variant
Dim y As Variant
Dim i As Variant
Dim count As Variant
Dim count1 As Variant
Dim count2 As Variant

Dim link As String

Dim olApp As Outlook.Application

Dim sat As String
Dim satcc As String
Dim j(200000)

For p = 1 To 200000
    j(p) = 0
Next p

count = 0
count1 = 0
count2 = 0

Dim mail As String

Dim tel As String

mail = Sheets("Macro").Range("f8")
tel = Sheets("Macro").Range("f9")

Call Import2

    Columns("b:b").Select
    Selection.NumberFormat = "m/d/yyyy"

Application.DisplayAlerts = True

Set olApp = New Outlook.Application

      a = Range("a1:a200000")
 
      b = Range("b1:b200000")

      d = Range("d1:d200000")
      m = Range("m1:m200000")
      
      f = Range("f1:f200000")
      g = Range("g1:g200000")
      
     jk = Range("j1:j200000")
      count1 = 0

 ebody1 = "" + "" + "Contract ID" + ""
    
    For i = 2 To 200000
        
       
        If j(i) = 0 And a(i, 1) <> "" And CStr(f(i, 1)) <> "" Then
        
      count = 0
        count1 = 0
        count2 = 0
     
        For k = 2 To 200000
       
               If CStr(g(k, 1)) = CStr(g(i, 1)) And j(k) = 0 And a(k, 1) <> "" And CStr(f(i, 1)) <> "" Then
              
               count = count + 1
         
                count2 = count2 + 1
                j(k) = 1
             
         ebody = CStr(g(k, 1)) + "" + ebody
         
                End If
         
           Next
         
       ebody = ebody1 + ebody + "

" + "Date" + "
" + "Deal ID" + "
" + "Code" + "
" + CStr(b(k, 1)) + "
" + CStr(m(k, 1)) + "
" + CStr(jk(k, 1)) + "


<tbody>

</tbody>
"

Dim olMail As MailItem
Set olMail = olApp.CreateItem(olMailItem)

Dim body1 As String
Dim body2 As String
Dim body3 As String
Dim body4 As String
Dim body5 As String
Dim body6 As String
Dim body7 As String
Dim body8 As String
Dim body9 As String

body1 = "Dear " + CStr(d(i, 1)) + ","

  Dim App As Object
      Dim item As Object
      On Error GoTo ende
       
       esubject = CStr(d(i, 1)) + "- List of customer codes"
       'sendfrom = "fake@123"
       sendto = CStr(f(i, 1))
       Set App = CreateObject("Outlook.Application")
       Set itm = App.CreateItem(olMailItem)
       With itm
           .Subject = esubject
           .To = sendto
           .CC = ccto
           .HTMLBody = body1 + body22 + body2
           body2 = ""
            
       If (Sheets("Macro").Range("E2") = "Draft") Then
            .Save
            End If
         If (Sheets("Macro").Range("E2") = "Send") Then
            .Send
            End If
       End With
       
       Set App = Nothing
       Set itm = Nothing
    ebody = ""
ende:
         ebody = ""
     End If
     
     Next

End Sub

Can anyone help please?

Thanks!

Tee

 

[teekayy]

bump?