×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Filtering and e-mailing result

Filtering and e-mailing result

Filtering and e-mailing result

(OP)
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? -

CODE --> VBA

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. peace

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

RE: Filtering and e-mailing result

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...

CODE

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}…

CODE

Set datasheet = Tabelle1
Set reportsheet = Sheet1 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Filtering and e-mailing result

(OP)
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

RE: Filtering and e-mailing result

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Filtering and e-mailing result

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

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 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

RE: Filtering and e-mailing result

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

CODE

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 strLineman As String
     Dim intFinalRow As Integer
     Dim i As Integer
     Dim strEdress As String
     Dim strSubj As String
     Dim strMessage As String 


---- Andy

There is a great need for a sarcasm font.

RE: Filtering and e-mailing result

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:
  • String = str
  • WorkBook = wb
  • WorkSheet = ws
  • Table = tbl (MS Access, generally)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close