auto mailing of selective range as attachment

E

Eddy Stan

Hi

I am trying to send automail to various persons of various departments from
finance, please help me. As it is voluminous data and repeated task. Let me
explain what I am doing
I have 2 sheets
1 detail data sheet – with autofilter ( I need to filter location code wise,
copy & paste in new work book and send as attachment to various people)
2 mailing information

Data in sheet 2 mailing information
Range a – Sl No
Range b – LocationCode
Range c – Location Name
Range d – Names (names of persons whose id is in range
Range e – To IDS
Range f - CC IDs
Range g – Instructions to type as body in each letter – customized based on
data, which I will type and keep in advance
Range h – Send / ignore – to validate before sending mail, choose to send
mail if the cell has word “Sendâ€
Range I - after sending mail, should store as “Sent†or “Skipped†(when
range h is changed to Send from ignore then mail need to be sent)

The macro should run automatically till the end of list in sheet 2 (70
locations approx), Auto mail is checked in Outlook so I can confirm manually,
bypass that checking is not available.

Can this be automated, please answer me by monday
 
E

Eddy Stan

HI
CAN I USE RANGE NAME AS CREDITNOTE INSTEAD OF A1:K50
HOW CAN I COPY THE RANGE TO NEW SHEET AND SAVE THAT AS AN ATTACHMENT FILE
ANY HELP SPECIFIC
 
E

Eddy Stan

Hi
I already have modified your coding to send auto mail with with 1 line
subject and now i want to attach file, the file name i will input in one
column. i need code to pick that file from default directory. I have code as
below

Sub TestFile()
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range



Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon



On Error GoTo cleanup
For Each cell In
Sheets("Sheet1").Columns("B").Cells.SpecialCells(xlCellTypeConstants)
If cell.Value Like "?*@?*.?*" And LCase(cell.Offset(0, 1).Value) =
"yes" Then
Set OutMail = OutApp.CreateItem(0)



On Error Resume Next
With OutMail
.To = cell.Value
.Subject = cell.Offset(0, 3).Value
.Body = "Dear " & cell.Offset(0, -1).Value & vbNewLine &
vbNewLine & _
cell.Offset(0, 2).Value
' "Please contact us to discuss bringing your account
up to date"
'You can add files also like this
'.Attachments.Add ("C:\test.txt")
.Send 'Or use Display
End With

On Error GoTo 0


Set OutMail = Nothing
End If
Next cell


cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub
 
E

Eddy Stan

Hi,
My mail is going but it is not picking the attachment, can u help please
' Set rng = sh.Cells(cell.Row, 1).Range("C1:Z1")
Set rng = sh.Cells(cell.Row, 1).Range("F3:H5")
i have names of file for attaching with full path (is it necesary for full
path ?)
attachments can be 1 or 2 or 3 or even 5 max
so how should i set the above line.

thank you.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Mail a different file(s) to each person in a range 0
Grouping Names in Mail Merge 0
Outlook Desktop (latest) stopped syncing IMAP accpounts from selfowned Mail server. 0
Clean this select case code up a bit. 2
E-mail excel attachment via Lotus Notes 0
Excel 2010 Sendmail .Display works, but not .Send --- Please help 2
e-mail a range of cells as the body 7
use ComboBox value to select range and then use selected range tocreate a chart 3

Top