Filtering and e-mailing result

[Rossco82]

Hi All,

I'm slightly lost with this and have had to resort to forums as I just cannot see what is wrong with the code.

In short I have three tabs,
Tabelle1 List of data, columns A_L.
Sheet 1 A data validation set in A1 which refers to a a list of Line Managers, and a button to activate the code.
Formulae Where I have converted the data from Tabelle1 into usable format.

In Tabelle1 column A there is a list of line managers, I have used the Formulae tab to remove some text after their names, and also create their e-mail addresses as we use a standardized format. So on the Formulae Tab I have the original entry in column F, and their correct e-mail address in column K. (Full range being K2-K98).

In the below code my aim is to filter by column A in Tabelle1, and attache the associated data to an email and send it to the recipient based on the e-mail address from the Formulae tab. However despite haviong defined the erange object, it still says it isn't defined? -

Option Explicit

Sub searchandcopy()
Dim datasheet As Worksheet
Dim Formulae As Worksheet
Dim Tabelle1 As Worksheet
Dim reportsheet As Worksheet
Dim Lineman As String
Dim finalrow As Integer
Dim i As Integer
Dim edress As String
Dim subj As String
Dim message As String
Dim filename As String
Dim outlookapp As Object
Dim outlookmailitem As Object
Dim myAttachments As Object
Dim path As String
Dim attachment As String
Dim erange As Range

Set erange = Formulae.Cells("k2:K98")

Set datasheet = Tabelle1
Set reportsheet = Sheet1
Lineman = reportsheet.Range("A1").Value
edress = Application.WorksheetFunction.VLookup(Lineman, erange, 1, False)

reportsheet.Range("B1").Value = edress

reportsheet.Range("A2:L1000").ClearContents

datasheet.Select
finalrow = Cells(Rows.Count, 1).End(xlUp).Row

For i = 4 To finalrow
    If Cells(i, 1) = Lineman Then
    Range(Cells(i, 3), Cells(i, 9)).Copy
    reportsheet.Select
    Range("A200").End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormulasAndNumberFormats
    datasheet.Select
    
    End If
Next i

reportsheet.Select
Set outlookapp = CreateObject("Outlook.Application")
    Set outlookmailitem = outlookapp.createitem(0)
    Set myAttachments = outlookmailitem.Attachments

path = "C:\Users\extRamsay\Documents\statements\"
    Application.DisplayAlerts = False
    
  filename = Lineman & ".pdf"
    subj = Lineman
    
  ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:= _
            path + filename, _
            Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
            :=False, OpenAfterPublish:=False
    
    attachment = path + filename
        
        outlookmailitem.To = edress
        outlookmailitem.cc = ""
        outlookmailitem.bcc = ""
        outlookmailitem.Subject = subj
        outlookmailitem.body = "Please find a copy of your user roles attached" & vbCrLf & "Best Regards"
           
            
        myAttachments.Add (attachment)
        outlookmailitem.display
        'outlookmailitem.send
        Application.DisplayAlerts = True
        
        Set outlookapp = Nothing
        Set outlookmailitem = Nothing
        
         



Range("A1").Select



End Sub

Any help would be appreciated. Or if someone has a better way of doing this, I'm all for it.

Thanks in advance.


A wise man once said....
"I think, therefore I yam."
SkipVought 25 Oct 18 12:11

 

[SkipVought]

Hi,

Without getting too deep into your code, I see your first problem.

You have three Worksheets names "Tabelle1" "Formulae" & "Sheet 1". (You also have another Worksheet Object variable in your VBA, with the name reportsheet.)

Before you can user any of your variables that are Objects, you must have a statement to Set each of them to a particular instance of that Object, ie...

Set Formulae = Worksheet("Formulae")
Set erange = Formulae.Cells("k2:K98")

Also I see no need to have multiple variables referring to the same object like {datasheet & Tabelle1} or {reportsheet & Sheet1}…

Set datasheet = Tabelle1
Set reportsheet = Sheet1

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[Rossco82]

Big sigh.........

So tried that, now getting "Sub or function not defined". It highlights the "Worksheet" text, and also the first line (the sub). I had moved on a little bit, but this has put me back to square one now. Slowly losing the will with it.

I went and recreated it and set all variables etc, this immediately gave the same error.

A wise man once said....
"I think, therefore I yam."
SkipVought 25 Oct 18 12:11

 

[SkipVought]

Please post your current code.

BTW, "I think, therefore I yam," is the translation of yet another quote of René Descartes, found in a dusty bin under some petrified crepes, "Cogito ergo spud."

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[kjv1611]

In my very humble opinion, you really need to reword your variables and also specify that they are within the ActiveWorkbook or else a specific workbook JUST IN CASE something else runs in the meantime. I know I wouldn't want to troubleshoot the code if I had to think hard just to find the Worksheet variables.

Here's how I'd suggest (something like this) for declaring your variables. Also, in your actual code, I'd seriously suggest indenting the code within each procedure. White space, including indentation, is BIG help in glancing at code and understanding what's going on and where

Sub SearchAndCopy()
     Dim wb As Workbook
     Dim wsData As Worksheet
     Dim wsFormulae As Worksheet
     Dim wsTabelle1 As Worksheet
     Dim wsReport As Worksheet
     Dim rE As Range 'instead of erange

     Dim Lineman As String
     Dim finalrow As Integer
     Dim i As Integer
     Dim edress As String
     Dim subj As String
     Dim message As String
     Dim filename As String
     Dim outlookapp As Object
     Dim outlookmailitem As Object
     Dim myAttachments As Object
     Dim path As String
     Dim attachment As String

     Set wb = ActiveWorkbook
     Set wsData = wb.Worksheets("Data")
     Set wsFormulae = wb.Worksheets("Formulae")
     '.... etc .... 
     Set rE = wsFormulae.Cells("k2:K98")

I also like to put a line break between at least object type variables and string/numeric type variables. Just easier for me when I go back and troubleshoot.

Your latest error/issue, I THINK has to do with Excel basically not knowing where to find the worksheet, but without seeing the code and knowing your exact circumstances, I could be totally wrong.

And once you make whatever further changes, it'd be good to post your code at that point in time (as suggested by the ever-wise Skip) so one of us lurkers can actually see what's going on for whatever other issues you have.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[Andrzejek]

Since you suggest to 'reword your variables', why not be consistent and use pre-fixes for all of used variables?

CODE
Sub SearchAndCopy()
     Dim wb As Workbook
     Dim wsData As Worksheet
     Dim wsFormulae As Worksheet
     Dim wsTabelle1 As Worksheet
     Dim wsReport As Worksheet
     Dim rE As Range 'instead of erange

     Dim [blue]str[/blue]Lineman As String
     Dim [blue]int[/blue]FinalRow As Integer
     Dim i As Integer
     Dim [blue]str[/blue]Edress As String
     Dim [blue]str[/blue]Subj As String
     Dim [blue]str[/blue]Message As String

---- Andy

There is a great need for a sarcasm font.

 

[kjv1611]

True - just didn't take the time to mess with the others.

For reference, OP, if you're not aware, the Leszynski/Reddick Guidelines:

http://access.mvps.org/access/general/gen0012.htm

You can also just search for anything to do with Reddick Naming Convention, and you'll find lots of resources on it.

Or you can just think about it:
[ul]
[li]String = str[/li]
[li]WorkBook = wb[/li]
[li]WorkSheet = ws[/li]
[li]Table = tbl (MS Access, generally)[/li]
[/ul]

Of course, you don't have to follow any one person's naming scheme: it's your code. But for sharing it or getting others to review it / help with it, it certainly helps to digest more easily.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57