Email Macro

May 6, 2010 at 14:46:32
Specs: Windows XP

Hi All,

I've recently joined this forum and received numerous helps via direct replies and searching existing topics. However, I may need to ask for everyone's help again.

My workbook is already finished (thanks to everyone's help here) and the final step will be to send it via email to different recipients, depending on the person using the workbook.

Is there a macro I can assign to a button which will send a worksheet to a recipient that I will select from a drop down list. The drop down is just a bonus, if it can't be done i guess it will be okay. Thanks!!!

See More: Email Macro

Report •

May 6, 2010 at 19:57:13
This site contains code for sending a workbook or a single sheet from workbook to an email recipient:

I haven't fully tested it, but...

1 - Put your email addresses in a Drop Down in A1
2 - Copy the code to send a Single Worksheet to the module for the sheet with the drop down
3 - Wrap the code inside a WorkSheet_Change macro
4 - Add/Change the line below

If Target.Address = "$A$1" Then
        myRecipient = Target.Value
'...The first part of the code
'Change this line:
    .SendMail ""

'To be:
    .SendMail myRecipient, _
'The rest of the code
End If

Report •

May 7, 2010 at 09:03:27
Hi. Thanks for the link. I tried to follow your instructions but can't quite get it.

Here is what I have so far... Instead of doing a worksheet change event, I created a sub using recorded macros and some stuff i found while reading through here. I did not do a worksheet change because there was an existing worksheet change already on the particular sheet (remember where you helped me to copy rows if cell is equal to text, thanks for that by the way). So I did a Sub and everything works fine since it is okay for me to assign this to a button anyway. Here is the code I am currently using

Sub send()
'extract sheet to another workbook

'delete rows using colum P as reference
Dim i, j As Integer
'define range
j = Range("P" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
For i = 1 To j
'delete if target is empty
    If Range("P" & i) = Empty Then
    End If
Next i
Application.ScreenUpdating = True

' Works in Excel 97 through Excel 2007.
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim Sourcewb As Workbook
Dim Destwb As Workbook
Dim TempFilePath As String
Dim TempFileName As String

With Application
   .ScreenUpdating = False
   .EnableEvents = False
End With

Set Sourcewb = ActiveWorkbook
' Using ActiveSheet.Copy creates a new workbook with
' the sheet and the file format is the same as the
' original workbook.
' Copy the worksheet to a new workbook.
Set Destwb = ActiveWorkbook

' Determine the Excel version and file extension/format.
With Destwb
   If Val(Application.Version) < 12 Then
      ' You are using Excel 97-2003.
      FileExtStr = ".xls": FileFormatNum = -4143
      ' You are using Excel 2007.
      ' When you use ActiveSheet.Copy to create a workbook,
      ' you are prompted with a security dialog. If you click No
      ' in the dialog, then the name of Sourcewb is the same
      ' as Destwb and you exit the subroutine. You only see this
      ' dialog when you attempt to copy a worksheet from an .xlsm file with macros disabled.
      If Sourcewb.Name = .Name Then
         With Application
            .ScreenUpdating = True
            .EnableEvents = True
         End With
         MsgBox "Your answer is No in the security dialog."
         Exit Sub
         Select Case Sourcewb.FileFormat
            ' Code 51 represents the enumeration for a macro-free
            ' Excel 2007 Workbook (.xlsx).
            Case 51: FileExtStr = ".xlsx": FileFormatNum = 51
            ' Code 52 represents the enumeration for a
            ' macro-enabled Excel 2007 Workbook (.xlsm).
            Case 52:
               If .HasVBProject Then
                  FileExtStr = ".xlsm": FileFormatNum = 52
                  FileExtStr = ".xlsx": FileFormatNum = 51
               End If
            ' Code 56 represents the enumeration for a
            ' a legacy Excel 97-2003 Workbook (.xls).
            Case 56: FileExtStr = ".xls": FileFormatNum = 56
            ' Code 50 represents the enumeration for a
            ' binary Excel 2007 Workbook (.xlsb).
             Case Else: FileExtStr = ".xlsb": FileFormatNum = 50
         End Select
      End If
   End If
End With

' Change all cells in the worksheet to values, if desired.
''   With Destwb.Sheets(1).UsedRange
''      .Cells.Copy
''      .Cells.PasteSpecial xlPasteValues
''      .Cells(1).Select
''   End With
''Application.CutCopyMode = False

'Save the new workbook and then mail it.
   TempFilePath = Environ$("temp") & "\"
   TempFileName = "Part of " & Sourcewb.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")

With Destwb
   .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
      On Error Resume Next
 <b>  .SendMail "", _
      "Subject Line" </b>
      On Error GoTo 0
   .Close SaveChanges:=False
End With
' Delete the file you just sent.
Kill TempFilePath & TempFileName & FileExtStr

With Application
   .ScreenUpdating = True
   .EnableEvents = True
End With
End Sub

I already have a dropdown of email addresses in $W$1. But everytime I try to do what you advised, it says that myrecipient wasn't defined. How can I make it send the email to the email address selected from the dropdown in W1?

Report •

May 7, 2010 at 09:50:38

This is what I use to send e-mail from an Excel macro:

'create the e-mail object
    Set objEmail = CreateObject("CDO.Message")
    'set e-mail SMTP configuration
    With objEmail
        With .Configuration.Fields
            .Item("") _
                = ""
            .Item("") _
                = "30"
            .Item("") _
                = "2"
            .Item("") _
                = "25"
            .Item("") _
                = ""
            .Item("") _
                = "1"
            .Item("") _
                = ""
            .Item("") _
                = "ZZZZZZZZZZ"
        End With
        'create e-mail message
        .To = "MMMMMMM"
        .From = "NNNNNNNNN"
        .Subject = "Message from Excel"
        .TextBody = "My Excel message"
        'optional add attachment
        '.AddAttachment ()
        'send e-mail
    End With
    'remove the e-mail object
    Set objEmail = Nothing

If your outgoing SMTP needs a password it will be in plain text in the excel macro.
If you use this, then I suggest setting up an e-mail account for this use only - no other e-mails sent on this account - as well as password protecting the Macro.

Set the various values as required by your e-mail provider - settings should be the same as used in Outlook or similar.


Report •

Related Solutions

May 7, 2010 at 10:14:07
First, I see that you tried to bold some of the code in your post. You can't use the bold (or any other) tags inside the pre tags.

Second, just because you already have a worksheet change macro in your sheet doesn't mean you can have "another" one.

Actually, it would still be one macro, it would just do different things based on the specific change. e.g.

Private Sub Worksheet_Change(ByVal Target As Range)

'Determine if change was to Column J (10)
  If Target.Column = 10 Then
    'Run Copy Row Code
     Exit Sub
  End If
'Determine if change was to W1
   If Target.Address = "W$1" Then
    'Run email code
     Exit Sub
   End If
End Sub

Now, I don't have time to pour over your code in detail right now, but via a quick look, I don't see any place where you reference W1 and use the value as an email address.

.SendMail "" 

could become:

.SendMail Range("W1") 


myRecipient = Range("W1")
.SendMail myRecipient 

or something similiar to that...

Report •

May 7, 2010 at 10:48:50
Don't i need to define myRecipient first?

I'll try tinkering with what you gave. I'll try all of your suggestions.

Quick question though, on the code that i currently have. The first part is a recorded macro which extracts sheets to a different workbook. However, on the code for the email that I got from the link, it shows that the same stuff is being done, copying the sheet to a new workbook and saving it. Shouldn't i try to remove that part?

Report •

May 7, 2010 at 14:18:29
Don't i need to define myRecipient first?

If you are using Option Explicit, then you need to Declare the variable via the Dim statement. If not, VBA will create the variable for you the first time it encounters it.

I see that the code offered at the MSDN site didn't use Option Explicit, so while it's up to you whether you use it or not, it usually a good idea that you do.

See here for a pretty good discussion regarding the use variables and Option Explicit in VBA.

Report •

Ask Question