Results 1 to 10 of 10

Button macro questions

Threaded View

  1. #1
    Registered User
    Join Date
    02-22-2012
    Location
    Sweden
    MS-Off Ver
    Excel 2010
    Posts
    13

    Button macro questions

    Hi!
    Im EXTREMLY newbie at this so let me try and explain what im trying to do:

    Thing nr 1:
    • Where i work there are alot of places to eat.
    • I have created a list of all places
    • I want to do a button that, when i press it, it chooses 1 of the places randomly (the name of the place) and puts that in a cell.

    What i have done:
    I have made a =index function (in that cell) that randomly picks one in index, and a button that refreshes -> which randomly picks a place (works). However, due to other functions in my worksheet i can not use this "refresh" function since it ruins the rest.

    What i want:
    A button that checks the "index" (list of places) - randomly selects one, and puts that name in a cell (without any refresh function). In other words: Nothing in the cell where i want the place to pop up, but with a button that does the "randomselect" thing for me and puts one in the cell.
    PS: The Index of places is in another sheet and the range is from b3:b40

    Thing nr 2:

    I have created another button (copy paste from a website) that selects a persons email, writes the subject, etc. However: I want it also to select a couple of cells from another sheet and put the content of it in the body.

    Ill try to show the script first. Hope i can =/

    Sub Mail_Sheet_Outlook_Body()
        Dim rng As Range
        Dim OutApp As Object
        Dim OutMail As Object
        With Application
            .EnableEvents = False
            .ScreenUpdating = False
        End With
     
        Set rng = Nothing
        Set rng = Range("A2:H16")
        
        'You can also use a sheet name
        'Set rng = Sheets("YourSheet").UsedRange
    
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
     
        On Error Resume Next
        With OutMail
            .To = Range("E16")
            .CC = ""
            .BCC = ""
            .Subject = "Lunch"
            .Body = (rng)
            .Display   'or use .Send
        End With
        On Error GoTo 0
     
        With Application
            .EnableEvents = True
            .ScreenUpdating = True
        End With
     
        Set OutMail = Nothing
        Set OutApp = Nothing
    End Sub
    Now where it sais ".Body = (rng)" i simply what that "(rng)" to be a certain sheet between a "A2 and H16". and then to use that contant (within those cells) as text.

    -------------------

    Please notice that im EXTREMLY new at this, so i wont understand much of text. I just wonder if any kind soul can help with these 2 buttons.

    Kind regards,
    Doite
    Last edited by Baoli; 03-05-2012 at 01:16 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1