VBA using outlook

[Cease]

Hello,
I'm altering a code I have found to mail a sheet to a specific email address, where the email address itself is in the current sheet.

Most of this code was patchwork from examples I have found, and works if I enter an address in manually, though now I'm trying to have it based off of a particular cell in the sheet.

The code I have now is:

Sub Mail_Range()
' Works in Excel 2000, Excel 2002, Excel 2003, Excel 2007, Excel 2010, Outlook 2000, Outlook 2002, Outlook 2003, Outlook 2007, Outlook 2010.
    Dim Source As Range
    Dim Dest As Workbook
    Dim wb As Workbook
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim FileExtStr As String
    Dim FileFormatNum As Long
    Dim OutApp As Object
    Dim OutMail As Object
    Set Source = Nothing
    On Error Resume Next
    Set Source = Range("A1:cx550").SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
    If Source Is Nothing Then
        MsgBox "The source is not a range or the sheet is protected. " & _
               "Please correct and try again.", vbOKOnly
        Exit Sub
    End If
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    Set wb = ActiveWorkbook
    Set Dest = Workbooks.Add(xlWBATWorksheet)
    Source.Copy
    With Dest.Sheets(1)
        .Cells(1).PasteSpecial Paste:=8
        .Cells(1).PasteSpecial Paste:=xlPasteValues
        .Cells(1).PasteSpecial Paste:=xlPasteFormats
        .Cells(1).Select
        Application.CutCopyMode = False
    End With
    TempFilePath = Environ$("temp") & "\"
    TempFileName = "Selection of " & wb.Name & " " _
                 & Format(Now, "dd-mmm-yy h-mm-ss")
    If Val(Application.Version) < 12 Then
        ' You are using Excel 2000 or 2003.
        FileExtStr = ".xls": FileFormatNum = -4143
    Else
        ' You are using Excel 2007 or 2010.
        FileExtStr = ".xlsx": FileFormatNum = 51
    End If
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    With Dest
        .SaveAs TempFilePath & TempFileName & FileExtStr, _
                FileFormat:=FileFormatNum
        On Error Resume Next
        With OutMail
            .To = ActiveSheet.Range("h46").Cells.Value
            .CC = ""
            .BCC = ""
            .Subject = "Updated comments and/or professional attributes"
            .Body = "This displays so that you know the button has worked, although you still need to send this email if you want me to get the changes." & vbNewLine & vbNewLine & "You only need to send this once for all of changes you make. Thanks!" & vbNewLine & vbNewLine
            .Attachments.Add Dest.FullName
            .Display
        End With
        On Error GoTo 0
        .Close SaveChanges:=False
    End With
    Kill TempFilePath & TempFileName & FileExtStr
    Set OutMail = Nothing
    Set OutApp = Nothing
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub

What's listed in bold is usually "emailadress@place.com"

However this example returns the error:
Run-time error '1004': Application definite or object-defined error.

I'm not sure what I'm missing but any advice correction or direction would be greatly appreciated. Thanks!

 

[Cease]

Shamed to bumping this, can anyone tell me what I would need to do to have it put a specific cell in the To: box for an email?

Thanks!

 

[Ruddles]

.To = ActiveSheet.Range("h46").Value

 

[Cease]

Thanks!

That works, however if the cell value is an email address it returns the same error. If it's any other text it seems to be fine.

I also removed the hyperlink from the cell and it still errors out. Any idea if there's a different property or format I need to apply to the cell, in this case H46? I'm running excel 2007 in Windows XP Pro.

Thanks again for the help!

 

[Ruddles]

What happens if you put ="emailadress@place.com" in the cell?

 

[Cease]

Well it worked when I was at home, but now that I'm back at work it's kind of wierd.

Would having multiple, similar code/macros within a workbook cause problems?

This works if it's by itself on a new workbook/worksheet; but in a real workbook that already has a similar (practically the same) macro this won't work.

Any ideas?

Thanks again Ruddles, I really appreciate the time and help :D

 

[Ruddles]

If all your macros have different names - different by as little as one character or underscore - then VBA and Excel don't have any problems.

If you have two macros with identical names you can address them by prefixing with te module name (Sheet1, ThisWorkbook, Module1, etc).

But it's best to give all your macros different names.

If you're struggling to work out what's going on, do you know you can place 'breakpoints' in your code which force VBA to pause and allow you to see what piece of code is being executed and what your variables contain, either by mousing over them or by printing their values to the Immediate window (Ctrl-G). You can add and remove breakpoints whilst the code is paused and run to the next one with F5 or step through the code one statement at a time with F8.

It's a massively useful tool once you get to grips with it.

 

[Cease]

Wow that's awesome. Definitely something I need to try out, like you said that'd be extremely helpful to break each code down.

And you nailed it, they both had the same name.

Then is there any easy way to copy a macro to about 100 worksheets and give them all a different name?
like m1,m2....

Or is that just going to take copy/paste and a little patience?

Thanks so much again!

 

[Ruddles]

No, you probably wouldn't (want to) copy code in that way. You would probably place the code in a standard code module (rather than a worksheet code module) and write it in such a way that it would either know which worksheet you wanted it to run against (by using ActiveSheet to reference it, perhaps), or you would pass a parameter to the procedure telling it which worksheet to use.

Try this: create a new workbook with three sheets in it and place the following code in each of the worksheet code windows:-]

Sub x()
Call Foo(Me.Name)
End Sub

Now create a new standard code module (Insert > Module) and put this in it:-

Sub Foo(str As String)
MsgBox "I was called from " & ThisWorkbook.Name & "." & str
End Sub

Now go back to one of the worksheet code modules and press F5. See - Foo knows where it was called from. You'd use a similar technique to write your procedure so it would look to the correct worksheet depending on where it was called from.

Copying 100-odd code modules is not the way to go, especially if you're likely to be modifying the code in the future. You don't want to be faced with the task of copying them all over again. Plus they take up space.

You can pass any number of values to a procedure in this way, so however many variables you have to consider from one sheet to the next, you can account for them all in this way.

 

[Cease]

Awesome.

Makes a lot of sense to run it that way.

You are so my hero, thanks again for all your help (and patience with my ignorance with VBA)!