Results 1 to 11 of 11
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ASSIGN RNG IN TEXT FORMAT TO A VAR... (2000 sr 1)

    Is possible to assign the range of cells (in sheet attached) in a var in text format...
    I want to use to insert in email body similar attached txt...

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: ASSIGN RNG IN TEXT FORMAT TO A VAR... (2000 sr 1)

    Can't you use ActiveWorkbook.SendMail to e-mail the worksheet?

    Please don't use ALL CAPS in the subject of your post. Thank you.

  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ASSIGN RNG IN TEXT FORMAT TO A VAR... (2000 sr 1)

    Good idea and sorry for caps, good idea to send sheet.
    I have this macro it fill a range in TEMPLATE about a cicling for next.
    In this case after the first cicle is finished and the amcro fill the TEMPLATE ho to send email to user in column D of CORPORATE...(note TEMPLATE is into same wbook and is Hide and want to mintain this to hide)

    Example: the macro fill TEMPLATE with fist step, send sheet TEMPLATE to the user in column D of corporate, clear TEMPLATE go to the next steep of macro, fil TEMPLATE send to the second user ecc...

    Sub MATCH_PER_EMAIL()
    Dim I, II, III As Long
    Sheets("TEMPLATE").Columns("A").NumberFormat = "@"
    Application.ScreenUpdating = False
    For I = 2 To Sheets("CORPORATE").Range("H" & Rows.COUNT).End(xlUp).Row
    II = Sheets("CORPORATE").Cells(I, "H").End(xlToRight).Column
    For III = Sheets("CORPORATE").Cells(I, "H").Column To II
    With Sheets("GAF").Columns("H")
    Set C = .Find(Sheets("CORPORATE").Cells(I, III).Value, , , xlWhole)
    If Not C Is Nothing Then
    f = C.Address
    Do
    Sheets("TEMPLATE").Range("A" & Rows.COUNT).End(xlUp).Offset(1).Resize(, 2).Value = C.Offset(, -7).Resize(, 2).Value
    Sheets("TEMPLATE").Range("A" & Rows.COUNT).End(xlUp).Offset(, 2).Resize(, 8).Value = C.Offset(, -4).Resize(, 8).Value
    Sheets("TEMPLATE").Range("A" & Rows.COUNT).End(xlUp).Offset(, 10).Resize(, 3).Value = C.Offset(, 6).Resize(, 3).Value
    Set C = .FindNextŠ
    Loop Until f = C.Address
    End If

    End With
    Next

    'HERE TO SENED EMAIL

    Sheets("TEMPLATE").Range("A2:M5000").ClearContents

    Sheets("TEMPLATE").Range("A2").Select

    Next

    Application.ScreenUpdating = False
    End Sub

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: ASSIGN RNG IN TEXT FORMAT TO A VAR... (2000 sr 1)

    Try the following code:
    <code>
    Dim strRecipient As String
    ' Get recipient from column D on Corporate sheet
    strRecipient = Sheets("Corporate").Range("D" & I)
    ' Copy Template sheet to new workbook
    Sheets("Template").Copy
    ' Send e-mail
    ActiveWorkbook.SendMail Recipients:=strRecipient, _
    Subject:="Data from Sal21"
    ' Close new workbook without saving
    ActiveWorkbook.Close SaveChanges:=False</code>

  5. #5
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ASSIGN RNG IN TEXT FORMAT TO A VAR... (2000 sr 1)

    nice but is possible to insert a little text in body?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: ASSIGN RNG IN TEXT FORMAT TO A VAR... (2000 sr 1)

    With the SendMail method, the worksheet is sent as the body of the e-mail, and you can specify the subject. So if you want more text in the body of the e-mail, put it in the worksheet.

    Otherwise, you'll have to use a different method for sending the e-mail.

  7. #7
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ASSIGN RNG IN TEXT FORMAT TO A VAR... (2000 sr

    So, i use the simple nacro you have maked for me:-)...

  8. #8
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ASSIGN RNG IN TEXT FORMAT TO A VAR... (2000 sr

    This is not a question on "email"... but use the code MATCH_PER_EMAIL

    This macro make a filter from sheet CORPORATE and GAF...
    Now if i have this sheet attached (RETAIL_POE instead CORPORATE) is possoble to modifiy the code and insert another parameter for filter...
    in effect use the code but insert a new filter from column F or RETAIL_POE and column D in GAF...
    example:
    filtre all value in gaf refered retail_poe plus filter in H of retail_poe and column D of gaf

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: ASSIGN RNG IN TEXT FORMAT TO A VAR... (2000 sr

    Sal,

    This is becoming far too complicated for an outsider to understand. Sorry.

  10. #10
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ASSIGN RNG IN TEXT FORMAT TO A VAR... (2000 sr

    <img src=/S/confused.gif border=0 alt=confused width=15 height=20> Outsider, you?

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: ASSIGN RNG IN TEXT FORMAT TO A VAR... (2000 sr

    Sal,

    For you, all these worksheets (GAF, CORPORATE, ...) have a specific meaning. You know what each column in each sheet means. For someone like me, they are just incomprehensible words and numbers.

    When you want to do something relatively simple with them, it is possible to help, but you are making things more and more complicated. I simply can't understand any more what you are doing.

    Moreover, as I have explained before, assistance with such extremely detailed problems is not the purpose of the Lounge.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •