E-mail macro

[slipperyjim]

Hi,
I have a simple e-mail macro that works, however, someone that understands VBA would need to enter the email addresses if they are to change, which it is likely they will.

is it possible to set up this macro so that when clicked it will email out to a list of peole in a cell range?

the cell range: Sheet "Data", Range U17:U39

in these cells are the email addresses typed like this:
firstname.lastname @ business.com.au


not all of the cells in the range will be populated.

if possible could it be set up to just type in the front of the email address as the back is always the same e.g

firstname.lastname (macro code will add the @business.com.au bit)

thanks for any help offered.

 

[slipperyjim]

slipperyjim, is there any way to get this to work for emailing a UserFrom? i have been trying for ages but getting nowhere. sheets are ok but the UserForm is a different beast.

is there a way to send this as an attachment?

 

[slipperyjim]

slipperyjim, is there any way to get this to work for emailing a UserFrom? i have been trying for ages but getting nowhere. sheets are ok but the UserForm is a different beast.

perhaps this line may help with sending

.Attachments.Add ("enter:\filepath\here.txt")


i dunno. is the user form a file, book or sheet? i don't think have ever made one lol

 

[Michael M]

Hi Diddi
What's this, the OZ Excel forum ???.....LOL
Have a look here....

http://www.mrexcel.com/forum/showthread.php?t=351866&highlight=email+userform

 

[daverunt]

Some code for attachments

With itm
.Subject = EmailSubject
.To = SendTo
.CC = ccTo
.Body = EmailBody
 ' AttachmentResponse = MsgBox("Click OK to Attach a file or Cancel to Send message with no attachment", vbOKCancel, "Attachment to Send?")
 ' If AttachmentResponse = vbOK Then
'AttachFileName = Application.GetOpenFilename("Files (*.**)," & _
'"*.**", 1, "Select File", "Open", False)
    
'.Attachments.Add (AttachFileName)
'End If
' Attachment Must be complete path (given by GetOpenFilename)
'.Display ' This property is used when you want
' the user to see email and manually send. Then
' comment out rest of code except “End With” statement
' and "End Sub" statement.
.send
MsgBox ("Mail sent to " & SendTo)
End With
Set App = Nothing
Set itm = Nothing
End Sub

 

[daverunt]

Blank cells ignored

At last:

Blank cells ignored. Maybe someone with more experience can code this a little better?

 For i = 17 To 39
    If Sheets("Data").Range("O17").Value <> "" Then
    Result = ";" & Sheets("Data").Range("O" & i).Value & "@chhwoodproducts.com.au"
    If Result = ";@chhwoodproducts.com.au" Then GoTo Line1
    nameList = nameList & ";" & Sheets("Data").Range("O" & i).Value & "@chhwoodproducts.com.au"
    Email_Send_To = nameList
    End If
 
Line1:
Next

 

[daverunt]

If you keep emails in separate columns for those in different countries you can just reuse the code.

  For i = 17 To 39
    If Sheets("Data").Range("O17").Value <> "" Then
    Result = ";" & Sheets("Data").Range("O" & i).Value & "@chhwoodproducts.com.au"
    If Result = ";@chhwoodproducts.com.au" Then GoTo Line1
    
    nameList = nameList & ";" & Sheets("Data").Range("O" & i).Value & "@chhwoodproducts.com.au"
    Email_Send_To = nameList
    End If
        
Line1:
Next
    For i = 17 To 39
    If Sheets("Data").Range("P17").Value <> "" Then
    Result2 = ";" & Sheets("Data").Range("P" & i).Value & "@chhwoodproducts.co.uk"
    If Result2 = ";@chhwoodproducts.co.uk" Then GoTo Line2
    
    nameList = nameList & ";" & Sheets("Data").Range("P" & i).Value & "@chhwoodproducts.co.uk"
    Email_Send_To = nameList
    End If
Line2:
Next

 

[diddi]

thanks to Michael M for useful link. do appreciate.
I should be able to work that in to what i have so far.

 

[slipperyjim]

If you keep emails in separate columns for those in different countries you can just reuse the code.

  For i = 17 To 39
    If Sheets("Data").Range("O17").Value <> "" Then
    Result = ";" & Sheets("Data").Range("O" & i).Value & "@chhwoodproducts.com.au"
    If Result = ";@chhwoodproducts.com.au" Then GoTo Line1
 
    nameList = nameList & ";" & Sheets("Data").Range("O" & i).Value & "@chhwoodproducts.com.au"
    Email_Send_To = nameList
    End If
 
Line1:
Next
    For i = 17 To 39
    If Sheets("Data").Range("P17").Value <> "" Then
    Result2 = ";" & Sheets("Data").Range("P" & i).Value & "@chhwoodproducts.co.uk"
    If Result2 = ";@chhwoodproducts.co.uk" Then GoTo Line2
 
    nameList = nameList & ";" & Sheets("Data").Range("P" & i).Value & "@chhwoodproducts.co.uk"
    Email_Send_To = nameList
    End If
Line2:
Next

yes thanks for that, i would prefer this option as entering a full e-mail makes the auot hyperlink turn on, and thats a pain in the butt.