date:20131119

$$Excel-Macros$$ Create a macro based on a range of cells in excel and send via Lotus Notes with attachment

2013-11-19 Thread Kim McLaughlin



I have a spreadsheet with columns A through AD and have about 1000 rows on 
my report. I need to run the report every month and send an email to anyone 
that has a greater than or equal to 6 in Column "W". If column "W" is 
greater than or equal to 6, then click a "send emails" button with an 
attachment of the worksheet to email recipients in column "P" via Lotus 
Notes. (There would be multiple addresses, and each month would be 
different depending on the criteria.) Also if the criteria does not match 
the 6, then delete the row, and always save a copy of the worksheet to my 
"C" drive. 

The code for the email with the subject, body text and attachment are 
working, but I am having trouble with selecting the range from the 
spreadsheet to send to the recipients and adding my signature to the end. 

Any help on this would be greatly appreciated! Thanks in advance.

Option Explicit

Const EMBED_ATTACHMENT As Long = 1454

Const stPath As String = "C:"


Sub Send_Active_Sheet()
 
  Dim stFileName As String
  
  Dim Worksheets As Variant
  
  Set Worksheets = Sheets("FabricsProjectList")

  Dim myDocument As Range
  
  Dim rng As Range

  Dim vaRecipients As String
  
  Dim vaCopyTo As Variant

  Dim noSession As Object

  Dim noDatabase As Object

  Dim noDocument As Object

  Dim noEmbedObject As Object

  Dim noAttachment As Object

  Dim stAttachment As String
  
  Dim stSubject As Variant
  
  Dim vaMsg As Variant

  Dim x As Integer
  
  Dim c As Range
  
  Dim i As Long
  
  Dim lRow As Long
  
  Dim lCol As Long

  Dim UserName As String
  
  Dim MailDbName As String
  
  Dim Maildb As Object
  
  Dim MailDoc As Object
  
  Dim AttachME As Object
  
  Dim Session As Object

  Dim stSignature As String

With Application
.ScreenUpdating = False
.DisplayAlerts = False

  
  'Copy the active sheet to a new temporarily workbook.

  With ActiveSheet

.Copy

stFileName = .Range("A1").Value

  End With
' Select range of e-mail addresses
   
Worksheets("FabricsProjectList").Range("P6", "P1000").Value
  
For i = 7 To Range("A" & Rows.Count).End(3)(2).Row
 
 '   Cells.Range ("W6:W1000")
   
   If Cells(i, "W:W") >= 6 Then
   
With Cells(i, "P:P")
   
   vaRecipients = Worksheets("FabricsProjectList").Range("P" & i).Value
   
   .SendTo = vaRecipients
  
  stSubject = "Hi, Enterprise Project Champion," & vbCrLf & "This is just a 
FYI - the last review of your Enterprise Project is older than 6 months...which 
one ? Please see audit list attached ..."

  vaMsg = "Hi," & vbCrLf & vbCrLf & "What I am looking for.. the reason 
for this reminder" & vbCrLf & vbCrLf & "It is my commitment" & vbCrLf & vbCrLf 
& "To run an audit every month" & vbCrLf & "To find out which projects are not 
in the regular review process (6 months)" & vbCrLf & "To send out this info to 
the champions and R&D leaders" & vbCrLf & vbCrLf & "Please be so kind and let 
me know if there have been RWW's/ reviews in the meantime. If Yes, please send 
me the documentation." & vbCrLf & vbCrLf & "We will enter the document and the 
new last review date into the database." & vbCrLf & vbCrLf & "Thank you"

  stSignature = 
Maildb.GetProfileDocument("CalendarProfile").GetItemValue("Signature")(0)

  stAttachment = stPath & "PROJECT TIME TRACKING\2013 Time Tracking 
Reports" & stFileName & "Fabrics R&D Time Tracking Reports_Sep2013_rev2.xls"
  
  
  End With
 

  'Save and close the temporarily workbook.

  With ActiveWorkbook

.SaveAs stAttachment

.Close

  End With

 
  'Instantiate the Lotus Notes COM's Objects.

  Set noSession = CreateObject("Notes.NotesSession")

  Set noDatabase = noSession.GETDATABASE("", "")

 
  'If Lotus Notes is not open then open the mail-part of it.

  If noDatabase.IsOpen = False Then noDatabase.OPENMAIL

 
  'Create the e-mail and the attachment.

  Set noDocument = noDatabase.CREATEDOCUMENT

  Set noAttachment = noDocument.CreateRichTextItem("stAttachment")

  Set noEmbedObject = noAttachment.EmbedObject(EMBED_ATTACHMENT, "", 
stAttachment)


  'Add values to the created e-mail main properties.

  With noDocument

.Form = "Memo"

.SendTo = vaRecipients

.CopyTo = vaCopyTo

.Subject = stSubject

.Body = vaMsg

.SaveMessageOnSend = True

.PostedDate = Now()

.SEND 0, vaRecipients

  End With
 

  'Delete the temporarily workbook.

  Kill stAttachment
 

  'Release objects from memory.

  Set noEmbedObject = Nothing

  Set noAttachment = Nothing

  Set noDocument = Nothing

  Set noDatabase = Nothing

  Set noSession = Nothing

  Set Maildb = Nothing
  
  Set MailDoc = Nothing
  
  Set Session = Nothing

  MsgBox "Congratulations! The e-mail has successfully been created and 
distributed", vbInformation

 
End If

Next i

End With

End Sub



-- 
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s 
=TIM

Re: $$Excel-Macros$$ Address of Shapes

2013-11-19 Thread Sam Mathai Chacko

The property TopLeftCell in itself is of type range. The code above can be
shorter.

Sub test()
Dim shp As Shape
For Each shp In ActiveSheet.Shapes
MsgBox shp.TopLeftCell.Address
Next
End Sub

Regards,
Sam Mathai Chacko


On Tue, Nov 19, 2013 at 2:22 PM, Chandra Shekar <
chandrashekarb@gmail.com> wrote:

> Hello,
>
> Thank you very much.
>
> Regards,
>
> Chandru
>
> On Mon, Nov 18, 2013 at 11:45 PM, ashish koul wrote:
>
>>  Sub test()
>> Dim shp As Shape
>>
>> For Each shp In ActiveSheet.Shapes
>> MsgBox Cells(shp.TopLeftCell.Row, shp.TopLeftCell.Column).Address
>> Next
>>
>>
>>
>> End Sub
>>
>>
>>
>> On Mon, Nov 18, 2013 at 1:38 PM, Chandra Shekar <
>> chandrashekarb@gmail.com> wrote:
>>
>>> Hello,
>>>
>>> Is there anyway to get cell reference of shapes please find sampe excel.
>>>
>>> Thanks in advance.
>>>
>>> Regards,
>>>
>>> Chandru
>>>
>>> --
>>> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be?
>>> It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
>>> https://www.facebook.com/discussexcel
>>>
>>> FORUM RULES
>>>
>>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>>> will not get quick attention or may not be answered.
>>> 2) Don't post a question in the thread of another member.
>>> 3) Don't post questions regarding breaking or bypassing any security
>>> measure.
>>> 4) Acknowledge the responses you receive, good or bad.
>>> 5) Jobs posting is not allowed.
>>> 6) Sharing copyrighted material and their links is not allowed.
>>>
>>> NOTE : Don't ever post confidential data in a workbook. Forum owners and
>>> members are not responsible for any loss.
>>> ---
>>> You received this message because you are subscribed to the Google
>>> Groups "MS EXCEL AND VBA MACROS" group.
>>> To unsubscribe from this group and stop receiving emails from it, send
>>> an email to excel-macros+unsubscr...@googlegroups.com.
>>> To post to this group, send email to excel-macros@googlegroups.com.
>>> Visit this group at http://groups.google.com/group/excel-macros.
>>> For more options, visit https://groups.google.com/groups/opt_out.
>>>
>>
>>
>>
>> --
>>  *Regards*
>>
>> *Ashish Koul*
>>
>>
>>  *Visit*
>> http://www.excelvbamacros.in
>> Like Us on 
>> Facebook
>> Join Us on Facebook 
>>
>>
>> P Before printing, think about the environment.
>>
>>
>>
>> --
>> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
>> =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
>> https://www.facebook.com/discussexcel
>>
>> FORUM RULES
>>
>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.
>> 2) Don't post a question in the thread of another member.
>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.
>> 4) Acknowledge the responses you receive, good or bad.
>> 5) Jobs posting is not allowed.
>> 6) Sharing copyrighted material and their links is not allowed.
>>
>> NOTE : Don't ever post confidential data in a workbook. Forum owners and
>> members are not responsible for any loss.
>> ---
>> You received this message because you are subscribed to the Google Groups
>> "MS EXCEL AND VBA MACROS" group.
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to excel-macros+unsubscr...@googlegroups.com.
>> To post to this group, send email to excel-macros@googlegroups.com.
>> Visit this group at http://groups.google.com/group/excel-macros.
>> For more options, visit https://groups.google.com/groups/opt_out.
>>
>
>  --
> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
> =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
> https://www.facebook.com/discussexcel
>
> FORUM RULES
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
> 2) Don't post a question in the thread of another member.
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
> 4) Acknowledge the responses you receive, good or bad.
> 5) Jobs posting is not allowed.
> 6) Sharing copyrighted material and their links is not allowed.
>
> NOTE : Don't ever post confidential data in a workbook. Forum owners and
> members are not responsible for any loss.
> ---
> You received this message because you are subscribed to the Google Groups
> "MS EXCEL AND VBA MACROS" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to excel-macros+unsubscr...@googlegroups.com.
> To post to this group, send email to excel-macros@googlegrou

$$Excel-Macros$$ Re:

2013-11-19 Thread Basole

See this..


http://www.addictivetips.com/windows-tips/enable-all-macros-in-excel-2010/
http://office.microsoft.com/en-us/excel-help/enable-or-disable-macros-in-office-files-HA010354316.aspx

regards
Basole


Em sábado, 16 de novembro de 2013 11h09min57s UTC-2, sharad escreveu:
>
> The attach file has a worksheet named Sub-Ledger which contains List 
> of Sundry Creditors with their address and other detail 
>
> i want the list of sundry creditors as per the format provided in 
> sheet named as Creditor detail 
>
> regards 
> Sharad 
> 09830083263 
>

-- 
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s 
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ 
https://www.facebook.com/discussexcel

FORUM RULES

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.

NOTE  : Don't ever post confidential data in a workbook. Forum owners and 
members are not responsible for any loss.
--- 
You received this message because you are subscribed to the Google Groups "MS 
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/groups/opt_out.

Re: $$Excel-Macros$$ Stuck in attaching multiple image files in email body

2013-11-19 Thread Farrukh Shaikh

i have done that also .! & some of the images are attached as an attachment
and against them there is a red cross in the email body, and some images
are attached in the email body.




On Tue, Nov 19, 2013 at 2:22 PM,  wrote:

> You need to attach the images as well
> Sent on my BlackBerry® from Vodafone
> --
> *From: * Farrukh Shaikh 
> *Sender: * excel-macros@googlegroups.com
> *Date: *Tue, 19 Nov 2013 13:10:50 +0500
> *To: *
> *ReplyTo: * excel-macros@googlegroups.com
> *Subject: *Re: $$Excel-Macros$$ Stuck in attaching multiple image files
> in email body
>
> Thanks ashish koul,  i tried and its working now but in a slightly
> different way .! here is the line :
>
> uuu = Mid(sImgPath, InStrRev(sImgPath, "\") + 1)
> HTML = HTML + ""
> .HTMLbody HTML
>
> But now the images in the body are shown as red cross rather then the
> whole image.
> suggestions are anxiously awaited.
>
> Regards.
>
>
> On Mon, Nov 18, 2013 at 11:29 PM, ashish koul wrote:
>
>> try something like this
>> Sub sample_macro()
>>
>>
>> Dim objOL As Object
>> Dim olMail As Object
>> Dim bdy As String
>>
>>
>> For Each itm In Array("C:\Users\Public\Pictures\Sample
>> Pictures\desert.jpg", "C:\Users\Public\Pictures\Sample Pictures\tulips.jpg")
>> bdy = bdy & "> InStrRev(itm, "\") + 1) & """  width=700 height=500 >  "
>> Next itm
>>
>>
>>  'send the email
>>
>> Set objOL = CreateObject("Outlook.Application")
>> Set olMail = objOL.CreateItem(olMailItem)
>>
>> With olMail
>> .To = "koul.ash...@gmail.com"
>> .Subject = "Add Chart in outlook mail body"
>>  For Each itm In Array("C:\Users\Public\Pictures\Sample
>> Pictures\desert.jpg", "C:\Users\Public\Pictures\Sample Pictures\tulips.jpg")
>>  .Attachments.Add itm
>>  Next
>> .HTMLBody = "hello " & bdy & " thanks"
>> .Display
>> End With
>>
>>
>> Set olMail = Nothing
>> Set olApp = Nothing
>>
>> End Sub
>>
>>
>>
>>
>> replace for loop with ur excat loop
>>
>>
>> On Fri, Nov 15, 2013 at 9:56 PM, Farrukh Shaikh wrote:
>>
>>> Dear Coders,
>>> iam new to VBA, was trying to automate my work, this is the last step of
>>> my task, some images are stored at a path ( here the path is in sImgPath )
>>> when i try to insert the image in the email, first image is inserted
>>> properly, but when there is the second iteration, it deletes the previous
>>> complete email body and insert the new image. i have died trying stuff to
>>> bypass this issue.
>>> please help me out, would be really grateful to you. ( also i can not
>>> replications of code as the number of images stored on the path is not
>>> fixed, it can change on daily basis.)
>>>
>>> Thanks in advance.
>>>
>>> Here is the code i am using.
>>>
>>> Sub Create_Email()
>>>
>>> Dim objOL As Object
>>> Dim sImgPath As String
>>> Dim olMail As MailItem
>>> Dim sHi As String
>>> Dim sBody As String
>>> Dim sThanks As String
>>>
>>> Dim path_a As String, path_b As String, C_Path As String, path_before As
>>> String, image_names() As String
>>>
>>> path_before = Cells(7, 9).Value
>>>
>>> path_a = Cells(7, 10).Value ' contains path of issues folder
>>> 'path_b = Cells(8, 10).Value ' contais bsc name
>>> C_Path = path_before & "\" & path_a ' Now C_Path has the complete path
>>> for taking images for attachemnet in the mail
>>>
>>> '** check number of files in the folders
>>>
>>>
>>> Dim MyFile As String
>>> Dim count As Integer, j As Integer
>>> MyFile = Dir(C_Path & "\" & path_b & "*.jpg")
>>>
>>> GoTo Line1
>>>
>>> ' this loop will store all the names of images in the folder
>>> to the array - > image_names()
>>>
>>> Do While MyFile <> ""
>>> MyFile = Dir
>>> If MyFile = "" Then
>>> GoTo Line2
>>> Else
>>> End If
>>>
>>> Line1:
>>> ReDim Preserve image_names(count)
>>> image_names(count) = MyFile
>>> count = count + 1   ' at the end of loop count will contain
>>> # of images
>>> Loop
>>> 'MsgBox j & " files found"
>>>
>>> 'Creating The EMAIL
>>>
>>> Line2:
>>> Set objOL = CreateObject("Outlook.Application")
>>> Set olMail = objOL.CreateItem(olMailItem)
>>> With olMail
>>>
>>> .To = "myem...@mydomain.com"
>>> .Subject = "NPO Issue :: Huawei :: " & path_a
>>> For j = 0 To count - 1
>>> sImgPath = C_Path & "\" & image_names(j)
>>> uuu = Mid(sImgPath, InStrRev(sImgPath, "\") + 1)
>>> sHi = "" & "Hi," & " " & "Here is
>>> the required solution: " & " "
>>>
>>> ' in the variable sImgPath i have the file name along with the extension
>>> ---> .jpg in my case
>>> sBody = ">> 350 > "
>>>
>>> olMail.HTMLBody = sBody
>>> .Display
>>> Next j
>>> End With
>>>
>>> 'Free-up the objects
>>> Set olMail = Nothing
>>> Set olApp = Nothing
>>>
>>> End Sub
>>>
>>>
>>>  --
>>> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be?
>>> It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
>>> https://www.facebook.com/discussexcel
>>>
>>> FORUM RULES
>>>

Re: $$Excel-Macros$$ Stuck in attaching multiple image files in email body

2013-11-19 Thread koul . ashish

You need to attach the images as well
Sent on my BlackBerry® from Vodafone

-Original Message-
From: Farrukh Shaikh 
Sender: excel-macros@googlegroups.com
Date: Tue, 19 Nov 2013 13:10:50 
To: 
Reply-To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Stuck in attaching multiple image files in email 
body

Thanks ashish koul,  i tried and its working now but in a slightly
different way .! here is the line :

uuu = Mid(sImgPath, InStrRev(sImgPath, "\") + 1)
HTML = HTML + ""
.HTMLbody HTML

But now the images in the body are shown as red cross rather then the whole
image.
suggestions are anxiously awaited.

Regards.


On Mon, Nov 18, 2013 at 11:29 PM, ashish koul  wrote:

> try something like this
> Sub sample_macro()
>
>
> Dim objOL As Object
> Dim olMail As Object
> Dim bdy As String
>
>
> For Each itm In Array("C:\Users\Public\Pictures\Sample
> Pictures\desert.jpg", "C:\Users\Public\Pictures\Sample Pictures\tulips.jpg")
> bdy = bdy & " InStrRev(itm, "\") + 1) & """  width=700 height=500 >  "
> Next itm
>
>
>  'send the email
>
> Set objOL = CreateObject("Outlook.Application")
> Set olMail = objOL.CreateItem(olMailItem)
>
> With olMail
> .To = "koul.ash...@gmail.com"
> .Subject = "Add Chart in outlook mail body"
>  For Each itm In Array("C:\Users\Public\Pictures\Sample
> Pictures\desert.jpg", "C:\Users\Public\Pictures\Sample Pictures\tulips.jpg")
>  .Attachments.Add itm
>  Next
> .HTMLBody = "hello " & bdy & " thanks"
> .Display
> End With
>
>
> Set olMail = Nothing
> Set olApp = Nothing
>
> End Sub
>
>
>
>
> replace for loop with ur excat loop
>
>
> On Fri, Nov 15, 2013 at 9:56 PM, Farrukh Shaikh wrote:
>
>> Dear Coders,
>> iam new to VBA, was trying to automate my work, this is the last step of
>> my task, some images are stored at a path ( here the path is in sImgPath )
>> when i try to insert the image in the email, first image is inserted
>> properly, but when there is the second iteration, it deletes the previous
>> complete email body and insert the new image. i have died trying stuff to
>> bypass this issue.
>> please help me out, would be really grateful to you. ( also i can not
>> replications of code as the number of images stored on the path is not
>> fixed, it can change on daily basis.)
>>
>> Thanks in advance.
>>
>> Here is the code i am using.
>>
>> Sub Create_Email()
>>
>> Dim objOL As Object
>> Dim sImgPath As String
>> Dim olMail As MailItem
>> Dim sHi As String
>> Dim sBody As String
>> Dim sThanks As String
>>
>> Dim path_a As String, path_b As String, C_Path As String, path_before As
>> String, image_names() As String
>>
>> path_before = Cells(7, 9).Value
>>
>> path_a = Cells(7, 10).Value ' contains path of issues folder
>> 'path_b = Cells(8, 10).Value ' contais bsc name
>> C_Path = path_before & "\" & path_a ' Now C_Path has the complete path
>> for taking images for attachemnet in the mail
>>
>> '** check number of files in the folders
>>
>>
>> Dim MyFile As String
>> Dim count As Integer, j As Integer
>> MyFile = Dir(C_Path & "\" & path_b & "*.jpg")
>>
>> GoTo Line1
>>
>> ' this loop will store all the names of images in the folder
>> to the array - > image_names()
>>
>> Do While MyFile <> ""
>> MyFile = Dir
>> If MyFile = "" Then
>> GoTo Line2
>> Else
>> End If
>>
>> Line1:
>> ReDim Preserve image_names(count)
>> image_names(count) = MyFile
>> count = count + 1   ' at the end of loop count will contain #
>> of images
>> Loop
>> 'MsgBox j & " files found"
>>
>> 'Creating The EMAIL
>>
>> Line2:
>> Set objOL = CreateObject("Outlook.Application")
>> Set olMail = objOL.CreateItem(olMailItem)
>> With olMail
>>
>> .To = "myem...@mydomain.com"
>> .Subject = "NPO Issue :: Huawei :: " & path_a
>> For j = 0 To count - 1
>> sImgPath = C_Path & "\" & image_names(j)
>> uuu = Mid(sImgPath, InStrRev(sImgPath, "\") + 1)
>> sHi = "" & "Hi," & " " & "Here is
>> the required solution: " & " "
>>
>> ' in the variable sImgPath i have the file name along with the extension
>> ---> .jpg in my case
>> sBody = "> 350 > "
>>
>> olMail.HTMLBody = sBody
>> .Display
>> Next j
>> End With
>>
>> 'Free-up the objects
>> Set olMail = Nothing
>> Set olApp = Nothing
>>
>> End Sub
>>
>>
>>  --
>> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
>> =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
>> https://www.facebook.com/discussexcel
>>
>> FORUM RULES
>>
>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.
>> 2) Don't post a question in the thread of another member.
>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.
>> 4) Acknowledge the responses you receive, good or bad.
>> 5) Jobs posting is not allow

Re: $$Excel-Macros$$ Address of Shapes

2013-11-19 Thread Chandra Shekar

Hello,

Thank you very much.

Regards,

Chandru

On Mon, Nov 18, 2013 at 11:45 PM, ashish koul  wrote:

>  Sub test()
> Dim shp As Shape
>
> For Each shp In ActiveSheet.Shapes
> MsgBox Cells(shp.TopLeftCell.Row, shp.TopLeftCell.Column).Address
> Next
>
>
>
> End Sub
>
>
>
> On Mon, Nov 18, 2013 at 1:38 PM, Chandra Shekar <
> chandrashekarb@gmail.com> wrote:
>
>> Hello,
>>
>> Is there anyway to get cell reference of shapes please find sampe excel.
>>
>> Thanks in advance.
>>
>> Regards,
>>
>> Chandru
>>
>> --
>> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
>> =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
>> https://www.facebook.com/discussexcel
>>
>> FORUM RULES
>>
>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.
>> 2) Don't post a question in the thread of another member.
>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.
>> 4) Acknowledge the responses you receive, good or bad.
>> 5) Jobs posting is not allowed.
>> 6) Sharing copyrighted material and their links is not allowed.
>>
>> NOTE : Don't ever post confidential data in a workbook. Forum owners and
>> members are not responsible for any loss.
>> ---
>> You received this message because you are subscribed to the Google Groups
>> "MS EXCEL AND VBA MACROS" group.
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to excel-macros+unsubscr...@googlegroups.com.
>> To post to this group, send email to excel-macros@googlegroups.com.
>> Visit this group at http://groups.google.com/group/excel-macros.
>> For more options, visit https://groups.google.com/groups/opt_out.
>>
>
>
>
> --
>  *Regards*
>
> *Ashish Koul*
>
>
>  *Visit*
> http://www.excelvbamacros.in
> Like Us on 
> Facebook
> Join Us on Facebook 
>
>
> P Before printing, think about the environment.
>
>
>
> --
> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
> =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
> https://www.facebook.com/discussexcel
>
> FORUM RULES
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
> 2) Don't post a question in the thread of another member.
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
> 4) Acknowledge the responses you receive, good or bad.
> 5) Jobs posting is not allowed.
> 6) Sharing copyrighted material and their links is not allowed.
>
> NOTE : Don't ever post confidential data in a workbook. Forum owners and
> members are not responsible for any loss.
> ---
> You received this message because you are subscribed to the Google Groups
> "MS EXCEL AND VBA MACROS" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to excel-macros+unsubscr...@googlegroups.com.
> To post to this group, send email to excel-macros@googlegroups.com.
> Visit this group at http://groups.google.com/group/excel-macros.
> For more options, visit https://groups.google.com/groups/opt_out.
>

-- 
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s 
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ 
https://www.facebook.com/discussexcel

FORUM RULES

1) Use concise, accurate thread titles. Poor thread titles, like Please Help, 
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get 
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.

NOTE  : Don't ever post confidential data in a workbook. Forum owners and 
members are not responsible for any loss.
--- 
You received this message because you are subscribed to the Google Groups "MS 
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/groups/opt_out.

Re: $$Excel-Macros$$ Stuck in attaching multiple image files in email body

2013-11-19 Thread Farrukh Shaikh

Thanks ashish koul,  i tried and its working now but in a slightly
different way .! here is the line :

uuu = Mid(sImgPath, InStrRev(sImgPath, "\") + 1)
HTML = HTML + ""
.HTMLbody HTML

But now the images in the body are shown as red cross rather then the whole
image.
suggestions are anxiously awaited.

Regards.


On Mon, Nov 18, 2013 at 11:29 PM, ashish koul  wrote:

> try something like this
> Sub sample_macro()
>
>
> Dim objOL As Object
> Dim olMail As Object
> Dim bdy As String
>
>
> For Each itm In Array("C:\Users\Public\Pictures\Sample
> Pictures\desert.jpg", "C:\Users\Public\Pictures\Sample Pictures\tulips.jpg")
> bdy = bdy & " InStrRev(itm, "\") + 1) & """  width=700 height=500 >  "
> Next itm
>
>
>  'send the email
>
> Set objOL = CreateObject("Outlook.Application")
> Set olMail = objOL.CreateItem(olMailItem)
>
> With olMail
> .To = "koul.ash...@gmail.com"
> .Subject = "Add Chart in outlook mail body"
>  For Each itm In Array("C:\Users\Public\Pictures\Sample
> Pictures\desert.jpg", "C:\Users\Public\Pictures\Sample Pictures\tulips.jpg")
>  .Attachments.Add itm
>  Next
> .HTMLBody = "hello " & bdy & " thanks"
> .Display
> End With
>
>
> Set olMail = Nothing
> Set olApp = Nothing
>
> End Sub
>
>
>
>
> replace for loop with ur excat loop
>
>
> On Fri, Nov 15, 2013 at 9:56 PM, Farrukh Shaikh wrote:
>
>> Dear Coders,
>> iam new to VBA, was trying to automate my work, this is the last step of
>> my task, some images are stored at a path ( here the path is in sImgPath )
>> when i try to insert the image in the email, first image is inserted
>> properly, but when there is the second iteration, it deletes the previous
>> complete email body and insert the new image. i have died trying stuff to
>> bypass this issue.
>> please help me out, would be really grateful to you. ( also i can not
>> replications of code as the number of images stored on the path is not
>> fixed, it can change on daily basis.)
>>
>> Thanks in advance.
>>
>> Here is the code i am using.
>>
>> Sub Create_Email()
>>
>> Dim objOL As Object
>> Dim sImgPath As String
>> Dim olMail As MailItem
>> Dim sHi As String
>> Dim sBody As String
>> Dim sThanks As String
>>
>> Dim path_a As String, path_b As String, C_Path As String, path_before As
>> String, image_names() As String
>>
>> path_before = Cells(7, 9).Value
>>
>> path_a = Cells(7, 10).Value ' contains path of issues folder
>> 'path_b = Cells(8, 10).Value ' contais bsc name
>> C_Path = path_before & "\" & path_a ' Now C_Path has the complete path
>> for taking images for attachemnet in the mail
>>
>> '** check number of files in the folders
>>
>>
>> Dim MyFile As String
>> Dim count As Integer, j As Integer
>> MyFile = Dir(C_Path & "\" & path_b & "*.jpg")
>>
>> GoTo Line1
>>
>> ' this loop will store all the names of images in the folder
>> to the array - > image_names()
>>
>> Do While MyFile <> ""
>> MyFile = Dir
>> If MyFile = "" Then
>> GoTo Line2
>> Else
>> End If
>>
>> Line1:
>> ReDim Preserve image_names(count)
>> image_names(count) = MyFile
>> count = count + 1   ' at the end of loop count will contain #
>> of images
>> Loop
>> 'MsgBox j & " files found"
>>
>> 'Creating The EMAIL
>>
>> Line2:
>> Set objOL = CreateObject("Outlook.Application")
>> Set olMail = objOL.CreateItem(olMailItem)
>> With olMail
>>
>> .To = "myem...@mydomain.com"
>> .Subject = "NPO Issue :: Huawei :: " & path_a
>> For j = 0 To count - 1
>> sImgPath = C_Path & "\" & image_names(j)
>> uuu = Mid(sImgPath, InStrRev(sImgPath, "\") + 1)
>> sHi = "" & "Hi," & " " & "Here is
>> the required solution: " & " "
>>
>> ' in the variable sImgPath i have the file name along with the extension
>> ---> .jpg in my case
>> sBody = "> 350 > "
>>
>> olMail.HTMLBody = sBody
>> .Display
>> Next j
>> End With
>>
>> 'Free-up the objects
>> Set olMail = Nothing
>> Set olApp = Nothing
>>
>> End Sub
>>
>>
>>  --
>> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
>> =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
>> https://www.facebook.com/discussexcel
>>
>> FORUM RULES
>>
>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.
>> 2) Don't post a question in the thread of another member.
>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.
>> 4) Acknowledge the responses you receive, good or bad.
>> 5) Jobs posting is not allowed.
>> 6) Sharing copyrighted material and their links is not allowed.
>>
>> NOTE : Don't ever post confidential data in a workbook. Forum owners and
>> members are not responsible for any loss.
>> ---
>> You received this message because you are subscribed to the Google Groups
>> "MS EXCEL AND VBA MACROS" group.
>> To unsubs

$$Excel-Macros$$ Create a macro based on a range of cells in excel and send via Lotus Notes with attachment

Re: $$Excel-Macros$$ Address of Shapes

$$Excel-Macros$$ Re:

Re: $$Excel-Macros$$ Stuck in attaching multiple image files in email body

Re: $$Excel-Macros$$ Stuck in attaching multiple image files in email body

Re: $$Excel-Macros$$ Address of Shapes

Re: $$Excel-Macros$$ Stuck in attaching multiple image files in email body

7 matches

Site Navigation

Mail list logo

Footer information